How to remove albhabet and leading zero in SQL Server

Question

I have code in SQL Server to remove characters from variable

DECLARE @textval NVARCHAR(30) SET @textval = 'CHA000382486' SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

And also different code to remove leading zeros

DECLARE @textval2 NVARCHAR(30) SET @textval2 = '000382486' SELECT SUBSTRING(@textval2, PATINDEX('%[^0]%', @textval2+'.'), LEN(@textval2))

But I want a single piece of code which would be able to remove alphabets and leading zero.

Can anyone help me?

If you convert to (big)int you loose your leading zero's (of course after removing other chars). If you then convert back to varchar again, you have your original datatype. — HoneyBadger
– HoneyBadger, Commented Dec 30, 2016 at 11:37

John Cappelletti · Accepted Answer · 2016-12-30 12:02:46Z

This will find the first numeric (non-zero) value

Declare @textval NVARCHAR(30) = 'CHA000382486' Select Substring(@textval,PatIndex('%[1-9]%',@textval),len(@textval))

Returns

Another option

Select ''+cast(right(@textval,9) as int)

Collectives™ on Stack Overflow

How to remove albhabet and leading zero in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related