Solved

(SQL)How to remove leading zeros

Posted on 2016-10-13
4
42 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

820 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