How to ignore all leading zeros in a number

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to ignore all leading zeros in a number

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-09-04 : 10:48:19

I have data coming this way:

001 - i want to show as 1
0234 - i want to show as 234
0012 - i want to show as 12
008 - i want to show as 8
000 - i want to show as just 0, there are some rows.

Thanks a lot for the helpful info.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-04 : 11:16:40

CAST it as INT. As in

CAST(YourCharacterColumn AS INT)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-09-04 : 12:07:55

SUBSTRING([data], ISNULL(NULLIF(PATINDEX('%[^0]%', [data]), 0), LEN([data])), 100)

For example:



SELECT 
    [data], 
    SUBSTRING([data], ISNULL(NULLIF(PATINDEX('%[^0]%', [data]), 0), LEN([data])), 100) AS result
FROM (
    SELECT '001' AS [data] UNION ALL
    SELECT '0234' UNION ALL -- i want to show as 234
    SELECT '0012' UNION ALL -- i want to show as 12
    SELECT '008' UNION ALL -- i want to show as 8
    SELECT '000' UNION ALL -- i want to show as just 0, there are some rows.
    SELECT '0000ABC000'
) AS derived

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-09-05 : 01:29:25

SELECT
ISNULL(NULLIF(CAST([data] AS INT),0),0) AS 'VALUE'
FROM
(
SELECT '001' AS [data] UNION ALL
SELECT '0234' UNION ALL
SELECT '0012' UNION ALL
SELECT '008' UNION ALL
SELECT '000'
) AS derived

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources