Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Removing leading zeroes in alpha-numeric string

Nov 19 2012 12:00AM by Madhivanan   

There was a question in the SQL forums that the user wanted to remove leading zeroes in the numbers from a varchar column. The column will have numbers, alphabets or alpha-numeric strings. Only numbers should be considered for removing the leading zeroes.

Consider the following set of data

create table #data (data varchar(10))
insert into #data 
select '12345' as data union all
select '00123' as data union all
select '000A1' union all
select '00D21' union all
select '00021' union all
select 'ANPJH' union all
select '20000' union all
select '00E33' 

There are two methods that I know

Method 1 : Use regular expression to find only the numbers and remove leading zeroes

select 
	data as before, 
	case when data not like '%[^0-9]%' then cast(cast(data as int) as varchar(10)) else data end as after 
from
	#data 

Method 2 : Use Try_convert() function to find only the numbers and remove leading zeroes
select 
	data as before, 
	case when try_convert(int,data) is not null then cast(cast(data as int) as varchar(10)) else data end as after 
from
	#data 

 

Both the queries return the following set of data

before     after
---------- ----------
12345      12345
00123      123
000A1      000A1
00D21      00D21
00021      21
ANPJH      ANPJH
20000      20000
00E33      00E33

Note: Method 1 will work in all versions whereas method 2 works from version 2012 onwards

Tags: 


Madhivanan
3 · 40% · 12924
2
 
0
Lifesaver
 
0
Refreshed
 
 
0
Incorrect



Submit

9  Comments  

  • Try adding this to your test data:

    UNION ALL SELECT '9999999999'

    And watch this error emerge for the first case:

    Msg 248, Level 16, State 1, Line 14 The conversion of the varchar value '9999999999' overflowed an int column.

    Suggest you refer your audience to the original forum question for better solutions: http://www.sqlservercentral.com/Forums/Topic1384345-392-1.aspx

    commented on Nov 19 2012 1:12AM
    Dwain Camps
    1490 · 0% · 14
  • Thanks Dwain. Using BIGINT will solve that problem. However for data whose length exceeds the maximum length of any numeral datatype, it is better to handle with string manipulation than datatype convertion.

    commented on Nov 19 2012 1:57AM
    Madhivanan
    3 · 40% · 12924
  • Ok. Considering only string manipulation and no datatype conversion, I would use

    select 
        data as before, 
        case when data not like '%[^0-9]%' then substring(data,patindex('%[^0]%',data),len(data)) else data end as after 
    from
        #data
    
    commented on Nov 19 2012 2:04AM
    Madhivanan
    3 · 40% · 12924
  • I find Madhivanan solutions (Considering only string manipulation and no datatype conversion) the best. A simpler solution is in my opinion always better But the TRY_CONVERT can be handy in other situations.

    commented on Nov 21 2012 3:27PM
    johan.parlevliet
    729 · 0% · 45
  • Thanks johan.parlevliet for your feedback

    commented on Nov 21 2012 10:39PM
    Madhivanan
    3 · 40% · 12924
  • You can remove leading zeros using PATINDEX, LEFT and RIGHT. The ISNULL and NULLIF are to handle the case when all digits are zero.

    CASE WHEN LEFT(Data, 1) = '0'
        THEN RIGHT(Data, LEN(Data) - ISNULL(NULLIF(PATINDEX('%[^0]%', Data), 0), LEN(Data)) + 1)
        ELSE Data
    END
    
    commented on Nov 28 2012 7:32AM
    Anthony Faull
    1250 · 0% · 20
  • Thanks Anthony Faull for the alternate method

    commented on Nov 29 2012 12:25AM
    Madhivanan
    3 · 40% · 12924
  • I can't see when it would be a better way but you did miss one method:

    select data [before]
          ,case when data like '%[^0-9]%'
                then data
                else replace(LTRIM(replace(data,'0',' ')),' ','0')
            end [after]
    from #data
    

    Clearly it won't work when the string might contain spaces but the rules here say it can't.

    commented on Nov 30 2012 9:43PM
    Rick Bielawski
    287 · 0% · 149
  • Thanks Rick Bielawski for showing another method

    commented on Dec 1 2012 1:19AM
    Madhivanan
    3 · 40% · 12924

Your Comment


Sign Up or Login to post a comment.

"Removing leading zeroes in alpha-numeric string" rated 5 out of 5 by 2 readers
Removing leading zeroes in alpha-numeric string , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]