SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to ignore all leading zeros in a number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

541 Posts

Posted - 09/04/2013 :  10:48:19  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 09/04/2013 :  11:16:40  Show Profile  Reply with Quote
CAST it as INT. As in
CAST(YourCharacterColumn AS INT)
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
425 Posts

Posted - 09/04/2013 :  12:07:55  Show Profile  Reply with Quote
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

Go to Top of Page

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
169 Posts

Posted - 09/05/2013 :  01:29:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000