(SQL)How to remove leading zeros

Hi guys,
   I using this query to get only digits from the [message]...
How to remove leading zeros as well?


SELECT LEFT(subsrt, PATINDEX('%[^0-9]%', subsrt + 't') - 1) 
FROM (
    SELECT subsrt = SUBSTRING([message], pos, LEN([message]))
        FROM (
			SELECT [message], pos = PATINDEX('%[0-9]%', [message])
			FROM Table
			) d
     ) t

Open in new window


 Thank You
table of results:
0000000000043559446
0000000000043559481
0000000000043559509
0000000000043559605
0000000000043559622
0000000000043559721
DariusAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<Knee-jerk answer> CAST the value as an integer, which by definition all numeric data types do not support leading zeros.
0
 
Russ SuterCommented:
Quickest way I can think of is to cast it to a number and back to a string like this:

SELECT CAST(CAST('00000123' AS NUMERIC) AS VARCHAR)
0
 
DariusAuthor Commented:
Guys, thank you
0
 
PortletPaulfreelancerCommented:
Just as an alternative that does not require type conversion::
SELECT
* 
from (
      select '0000000000043559446' as message
     ) your_table
outer apply (
 select SUBSTRING([message], PATINDEX('%[^0]%', message + '0'), LEN([message])) substr
 ) oa

Open in new window

That example would produce:
message             substr   
------------------- -------- 
0000000000043559446 43559446 

Open in new window

1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.