Trim my ID

Bone_12 361 Reputation points
2022-05-05T15:00:54.297+00:00

Hi,

I have different lengths within a number that follow a similar pattern, but my substring doesn't work due this.

Some IDs are 5/123456 and other IDs are 5/00123456

Any idea how to can just get to the number of 123456 as my output, please?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,269 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-06T01:51:23.943+00:00

    Hi @Bone_12
    Since you are using SUBSTRING function, and I guess the result you could get is '123456', '00123456'.
    If you want to get rid of the extra 0 in front of the number, there is a simple solution: Convert the data type of string from VARCHAR/NVARCHAR to INT/BIGINT, so that SQL Sever will automatically ignore the extra 0 in front of the number.
    Check this:

     Create Table #test (Number varchar(100))  
              
     Insert Into #test (Number) Values   
     ('5/123456')  
     ,('5/00123456')  
     ,('5/0000123456')  
      
    SELECT CAST(SUBSTRING(Number,CHARINDEX('/',Number,1)+1,LEN(Number))AS BIGINT)  New_Number  
    FROM #test  
    --Or  
    SELECT TRY_CAST(RIGHT(Number,LEN(Number)-CHARINDEX('/',Number,1)) AS BIGINT)  New_Number  
    FROM #test  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


2 additional answers

Sort by: Most helpful
  1. Naomi 7,366 Reputation points
    2022-05-05T15:10:44.277+00:00

    Try:

    DECLARE @t TABLE (id VARCHAR(100))
    
    INSERT into @t (id) VALUES ('5/123456'), ('5/00123456'), ('00088882'), ('aaaaa');
    
    SELECT TRY_CAST(SUBSTRING(t.id, y.pos + 1, LEN(id)) AS BIGINT) AS [newID], id
    
    FROM @t t
    CROSS APPLY (SELECT CHARINDEX('/', id) AS Pos) y
    
    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2022-05-05T16:02:28.697+00:00
    Create Table test
    (id int identity(1,1) primary key, col varchar(100))
    
    
    Insert Into test (col) Values 
    ('5/123456')
    ,('5/0000123456')--,(''),(null) 
    
     select try_cast(stuff(col,1, charindex('/',col),'') as int) as yourID
    from test
    
    --select id, try_cast(value as int) --,ordinal 
    --from test  
    --cross apply string_split(col,'/' ) t
    
    drop Table test
    
    0 comments No comments