Leading Zeros in alpha-numeric string - previous solutions didn't work

Question

Post reply

Leading Zeros in alpha-numeric string - previous solutions didn't work

P74

Old Hand

Points: 342
More actions
November 13, 2012 at 5:03 pm

#264633

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

Assuming that your fields are always five characters long, would this work:

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(5))

INSERT #Temp (PartNum) VALUES

('12345'),

('00123'),

('10000'),

('A1234'),

('12A34'),

('0A123'),

('000A1'),

('00D21'),

('00E33');

SELECT PartNum,

CASE

WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', PartNum) = 1 THEN CAST(CAST(PartNum AS int) AS varchar(5))

ELSE PartNum

END AS LeadingZerosStripped

FROM #Temp;

HTH,

Rob

P74 Old Hand Points: 342 More actions · Answer 2

thanks rob, this has certainly improved the results and helped with the leading zeros. the samples i provided where just a test. the actual data holds fields up to 18 char long.

it now gives a new error, i believe when converting to INT:

Msg 248, Level 16, State 1, Line 6

The conversion of the nvarchar value '000000002440222744' overflowed an int column.

what's the workaround for this?

cheers

paul

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

How about something like this?

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))

INSERT #Temp (PartNum) VALUES

('12345'),

('00123'),

('10000'),

('A1234'),

('12A34'),

('0A123'),

('000A1'),

('00D21'),

('00E33'),

('00000000000000000004560');

--SELECT PartNum,

-- CASE

-- WHEN PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', PartNum) = 1 THEN CAST(CAST(PartNum AS int) AS varchar(5))

-- ELSE PartNum

-- END AS LeadingZerosStripped

--FROM #Temp;

select *, patindex('%[^0]%', PartNum), len(PartNum), substring(PartNum, patindex('%[^0]%', PartNum), len(PartNum) - patindex('%[^0]%', PartNum) + 1) from #Temp;

drop table #Temp;

P74 Old Hand Points: 342 More actions · Answer 4

thanks lynn, but that is still removing zeros from alpha-numeric fields...

e.g.

for '012GR3NL' your code would return '12GR3NL'

rgrds

paul

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

P74 (11/13/2012)
thanks lynn, but that is still removing zeros from alpha-numeric fields...
e.g.
for '012GR3NL' your code would return '12GR3NL'
rgrds
paul

I'm sorry, but

need help removing leading zeros from alpha-numeric column:

So what you really want is to remove leading zeros from pure numeric data in alpha-numeric data columns, correct?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

So more like this then, right?

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))

INSERT #Temp (PartNum) VALUES

('12345'),

('00123'),

('10000'),

('A1234'),

('12A34'),

('0A123'),

('000A1'),

('00D21'),

('00E33'),

('00000000000000000004560');

select

*,

case when patindex('%[^0-9]%', PartNum) = 0

then substring(PartNum, patindex('%[^0]%', PartNum), len(PartNum) - patindex('%[^0]%', PartNum) + 1)

else PartNum

end

from

#Temp;

drop table #Temp;

Dwain Camps SSC Guru Points: 86908 More actions · Answer 7

Here's another way that seems to work with Lynn's set up data:

SELECT PartNum, RIGHT(PartNum,

LEN(PartNum) + CASE PATINDEX('%[^0-9]%', PartNum)

WHEN 0 THEN 1 - PATINDEX('%[^0]%', PartNum) ELSE 0 END)

FROM #Temp

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

P74 Old Hand Points: 342 More actions · Answer 8

P74

Old Hand

Points: 342

November 14, 2012 at 2:10 am

#1558800

Perfect! Many Thanks Lynn

YOU'RE A STAR!!!

P74 Old Hand Points: 342 More actions · Answer 9

hi dwain.

it didn't quite do the job for some records such as 'STI850-200 ' or '25-53492-22 ' where it drops the first digit...

very close to Lynn's results however.

thanks guys

Dwain Camps SSC Guru Points: 86908 More actions · Answer 10

P74 (11/14/2012)
hi dwain.
it didn't quite do the job for some records such as 'STI850-200 ' or '25-53492-22 ' where it drops the first digit...
very close to Lynn's results however.
thanks guys

Tried it with this test data so I guess I'm not seeing what you mean.

CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))

INSERT #Temp (PartNum) VALUES

('12345'),

('00123'),

('10000'),

('A1234'),

('12A34'),

('0A123'),

('000A1'),

('00D21'),

('00E33'),

('00000000000000000004560'),

('STI850-200'),

('25-53492-22'),

('0STI850-200'),

('025-53492-22');

SELECT PartNum, RIGHT(PartNum,

LEN(PartNum) + CASE PATINDEX('%[^0-9]%', PartNum)

WHEN 0 THEN 1 - PATINDEX('%[^0]%', PartNum) ELSE 0 END)

FROM #Temp

drop table #Temp;

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

dwain.c (11/14/2012)
P74 (11/14/2012)
hi dwain.
it didn't quite do the job for some records such as 'STI850-200 ' or '25-53492-22 ' where it drops the first digit...
very close to Lynn's results however.
thanks guys
Tried it with this test data so I guess I'm not seeing what you mean.
CREATE TABLE #Temp (Id int IDENTITY(1,1), PartNum varchar(25))
INSERT #Temp (PartNum) VALUES
('12345'),
('00123'),
('10000'),
('A1234'),
('12A34'),
('0A123'),
('000A1'),
('00D21'),
('00E33'),
('00000000000000000004560'),
('STI850-200'),
('25-53492-22'),
('0STI850-200'),
('025-53492-22');
SELECT PartNum, RIGHT(PartNum,
LEN(PartNum) + CASE PATINDEX('%[^0-9]%', PartNum)
WHEN 0 THEN 1 - PATINDEX('%[^0]%', PartNum) ELSE 0 END)
FROM #Temp
drop table #Temp;

I have to agree, Dwain's code seems to work for me as well.