Solved

(SQL)How to remove leading zeros

Posted on 2016-10-13
4
41 Views
Last Modified: 2016-10-13
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
Comment
Question by:Darius
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 41842686
<Knee-jerk answer> CAST the value as an integer, which by definition all numeric data types do not support leading zeros.
0
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 250 total points
ID: 41842687
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
 

Author Comment

by:Darius
ID: 41842751
Guys, thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41843062
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question