Solved

(SQL)How to remove leading zeros

Posted on 2016-10-13
4
34 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
Comment Utility
<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
Comment Utility
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
Comment Utility
Guys, thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now