| Author |
 Topic  |
|
|
luzippu
Starting Member
United Kingdom
21 Posts |
 Posted - 11/13/2012 : 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)
Singapore
16769 Posts |
Posted - 11/13/2012 : 19:51:51
|
what is your criteria ? why for 0A123 , 000A1 , 00D21 & 00E33 , the leading zero is not removed
KH
Time is always against us
|
 |
|
|
luzippu
Starting Member
United Kingdom
21 Posts |
Posted - 11/13/2012 : 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)
Singapore
16769 Posts |
|
|
luzippu
Starting Member
United Kingdom
21 Posts |
Posted - 11/14/2012 : 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
India
22469 Posts |
Posted - 11/17/2012 : 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 |
|
|
| |
Topic |
|
Leading Zeros in alpha-numeric string
