• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 110
  • Last Modified:

(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
0
Darius
Asked:
Darius
2 Solutions
 
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
 
PortletPaulCommented:
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now