Trim my ID

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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