Trim my ID

Bone_12 361 Reputation points


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
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

    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   
    SELECT CAST(SUBSTRING(Number,CHARINDEX('/',Number,1)+1,LEN(Number))AS BIGINT)  New_Number  
    FROM #test  
    SELECT TRY_CAST(RIGHT(Number,LEN(Number)-CHARINDEX('/',Number,1)) AS BIGINT)  New_Number  
    FROM #test  

    Best regards,

    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


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

  2. Jingyang Li 5,891 Reputation points
    Create Table test
    (id int identity(1,1) primary key, col varchar(100))
    Insert Into test (col) Values 
     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