Simple Code Challenge
Posted on 2004-11-25
I have the following string: CO_DD_MM_YY_HI
Fields CO, DD, MM, YY, HI represent numbers with leading 0's removed..
e.g. 1_12_93_2_8 ,
What I need to do, is split the string up so, I know what CO, DD, MM, YY, HI is into seperate fields..
CO DD MM YY HI
1 12 93 2 8
33 4 45 35 2
Due to the removing of leading 0's patindex has to be used to get each part of the string with a replace (@string,'_','#') for each patindex input.
This has to be a single T-SQL Statement, and cannot be a cursor/loop procedure.
As this may give you a head-ache in producing the code, I've assigned a suitable amount of points for a speedy solution.
This is to reformat a complete table structure of fields and perform the field splits as rqd (and wil be run many times -- hence the need for a good single T-SQL Statement to perform this work). I do not mind if you can produce a demo piece of code using a single value stored in a variable, as I will modify to run on the table.
To add to the complications, Some ROWS are formated like so:
1129401248 --> This data should not be converted and return 0 rows of data where the input format is incorrectly formated.
(Input format, to be checked by the number of _ present in the string -- should be at least 4 _ as given above).