Madhivanan's TSQL Blog

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


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

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

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (389) · Preferences

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:

Previous: Different ways to find DISTINCT values
Next: Different ways to find DISTINCT values - Faster methods

Madhivanan

3 · 39% · 12472

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is 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

1446 · 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 · 39% · 12472

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 · 39% · 12472

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

869 · 0% · 33

Thanks johan.parlevliet for your feedback

commented on Nov 21 2012 10:39PM

Madhivanan

3 · 39% · 12472

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

1220 · 0% · 20

Thanks Anthony Faull for the alternate method

commented on Nov 29 2012 12:25AM

Madhivanan

3 · 39% · 12472

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

284 · 0% · 149

Thanks Rick Bielawski for showing another method

commented on Dec 1 2012 1:19AM

Madhivanan

3 · 39% · 12472

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 +Madhivanan