Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Leading Zeros in alpha-numeric string - previous solutions didn't work Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 13, 2012 5:03 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 05, 2012 6:41 AM Points: 7, Visits: 24
 hi,need help removing leading zeros from alpha-numeric column:Before After12345 = 1234500123 = 12310000 = 10000A1234 = A123412A34 = 12A340A123 = 0A123 000A1 = 000A100D21 = 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
Post #1384345
 Posted Tuesday, November 13, 2012 5:37 PM
 Right there with Babe Group: General Forum Members Last Login: Today @ 11:10 AM Points: 740, Visits: 785
 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 LeadingZerosStrippedFROM #Temp;`HTH,Rob
Post #1384351
 Posted Tuesday, November 13, 2012 5:50 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 05, 2012 6:41 AM Points: 7, Visits: 24
 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 6The conversion of the nvarchar value '000000002440222744' overflowed an int column.what's the workaround for this?cheerspaul
Post #1384352
 Posted Tuesday, November 13, 2012 6:01 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:20 PM Points: 21,602, Visits: 27,428
 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;`
Post #1384353
 Posted Tuesday, November 13, 2012 6:18 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 05, 2012 6:41 AM Points: 7, Visits: 24
 thanks lynn, but that is still removing zeros from alpha-numeric fields...e.g.for '012GR3NL' your code would return '12GR3NL'rgrdspaul
Post #1384357
 Posted Tuesday, November 13, 2012 6:26 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:20 PM Points: 21,602, Visits: 27,428
 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'rgrdspaulI'm sorry, butneed 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?
Post #1384361
 Posted Tuesday, November 13, 2012 6:32 PM
 SSC-Insane Group: General Forum Members Last Login: Today @ 5:20 PM Points: 21,602, Visits: 27,428
 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 endfrom #Temp;drop table #Temp;`
Post #1384363
 Posted Wednesday, November 14, 2012 1:42 AM
 SSCrazy Group: General Forum Members Last Login: Yesterday @ 6:07 PM Points: 2,340, Visits: 3,167
 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` No loops! No CURSORs! No RBAR! Hoo-uh!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?Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Are you too recursively challenged?Splitting strings based on patterns can be fast!
Post #1384456
 Posted Wednesday, November 14, 2012 2:10 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 05, 2012 6:41 AM Points: 7, Visits: 24
 Perfect! Many Thanks LynnYOU'RE A STAR!!!
Post #1384472
 Posted Wednesday, November 14, 2012 2:34 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 05, 2012 6:41 AM Points: 7, Visits: 24
 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
Post #1384485

 Permissions