Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

luzippu
Starting Member

United Kingdom
23 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
17689 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
23 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
17689 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
23 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
22864 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  
 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.05 seconds. Powered By: Snitz Forums 2000