November 13, 2012 at 5:03 pm
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
November 13, 2012 at 5:37 pm
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
November 13, 2012 at 5:50 pm
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
November 13, 2012 at 6:01 pm
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;
November 13, 2012 at 6:18 pm
thanks lynn, but that is still removing zeros from alpha-numeric fields...
e.g.
for '012GR3NL' your code would return '12GR3NL'
rgrds
paul
November 13, 2012 at 6:26 pm
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?
November 13, 2012 at 6:33 pm
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;
November 14, 2012 at 1:42 am
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 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
November 14, 2012 at 2:10 am
Perfect! Many Thanks Lynn
YOU'RE A STAR!!!
November 14, 2012 at 2:34 am
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
November 14, 2012 at 2:44 am
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 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
November 14, 2012 at 7:10 am
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.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply