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)
 Leading Zeros in alpha-numeric string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

luzippu
Starting Member

United Kingdom
22 Posts

Posted - 11/13/2012 :  19:10:50  Show Profile  Reply with Quote

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)

Singapore
17586 Posts

Posted - 11/13/2012 :  19:51:51  Show Profile  Reply with Quote
what is your criteria ? why for 0A123 , 000A1 , 00D21 & 00E33 , the leading zero is not removed


KH
Time is always against us

Go to Top of Page

luzippu
Starting Member

United Kingdom
22 Posts

Posted - 11/13/2012 :  20:08:35  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17586 Posts

Posted - 11/13/2012 :  20:50:14  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

luzippu
Starting Member

United Kingdom
22 Posts

Posted - 11/14/2012 :  04:36:21  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 11/17/2012 :  06:33:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
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.07 seconds. Powered By: Snitz Forums 2000