Leading Zeros in alpha-numeric string

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)

Leading Zeros in alpha-numeric string

Author

Topic

luzippu
Starting Member

23 Posts

Posted - 2012-11-13 : 19:10:50

hi,

need help removing leading zeros from alpha-numeric column:

Before After
12345 = 12345
00123 = 123
10000 = 10000
A1234 = A1234
12A34 = 12A34
0A123 = 0A123
000A1 = 000A1
00D21 = 00D21 (ISNUMERIC will return 1 for this)
00E33 = 00E33 (ISNUMERIC will return 1 for this)

i've tried
a) LTRIM(SUBSTRING([Before], PATINDEX('%[^0]%',[Before]),5))
b) SUBSTRING([Before], PATINDEX('%[^0]%', [Before]), LEN([Before]))

Both are no good as they remove the leading zeros where you have a letter in the middle.
If you use a case statement (ISNUMERIC) the last 2 examples will be treated as numeric, hence will not work...

Any ideas?
thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-13 : 19:51:51

what is your criteria ? why for 0A123 , 000A1 , 00D21 & 00E33 , the leading zero is not removed

KH
[spoiler]Time is always against us[/spoiler]

luzippu
Starting Member

23 Posts

Posted - 2012-11-13 : 20:08:35

hi, the "after" column is how i want the results to show... i.e if alpha-numeric keep the leading zeros.

just received a solution using:
CASE
WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', [Before]) = 1
THEN CAST(CAST([Before] AS int) AS varchar(18))
ELSE [Before]
END

but the above is now causing a different error:
Msg 248, Level 16, State 1, Line 6
The conversion of the nvarchar value '000000002440222744' overflowed an int column.

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-13 : 20:50:14

using Kristen's kk_fn_UTIL_IsINT from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59049

coalesce(convert(varchar(10), dbo.kk_fn_UTIL_IsINT(Before)), Before)

KH
[spoiler]Time is always against us[/spoiler]

luzippu
Starting Member

23 Posts

Posted - 2012-11-14 : 04:36:21

fyi,
found an easy solution from another user:

case when patindex('%[^0-9]%', [ColumnName]) = 0
then substring([ColumnName], patindex('%[^0]%', [ColumnName]), len([ColumnName]) - patindex('%[^0]%', [ColumnName]) + 1)
else [ColumnName]
end

cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-17 : 06:33:25

Try something like this


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
	(
		select '12345' as data union all
		select '00123' as data union all
		select '000A1' union all
		select '00D21 ' union all
		select '00E33' 
	) as t

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources