Author |
Topic |
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-04 : 21:04:36
|
Hi,I have a SQL select that returns the following with string value like:Status|RangeWorking|00-0 TO 02-1 & 05-1 TO 06-1 & 40-1I have to create a function in SQL to get the following for the Range column values and the digit after the '-' can only be a 0 or a 1:Status|RangeWorking|00-0Working|00-1Working|01-0Working|01-1Working|02-0Working|02-1Working|05-1Working|06-0Working|06-1Working|40-1Any idea ?Thanks! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 07:56:15
|
If I were faced with this problem, the first thing I would try to do is to see if there is any way I can get the data in a more normalized form from the source. Assuming that that is not possible, these are the steps that I would do:1. Split the string into tokens on the ampersand separtor (&). There are string splitters available online - for example here:http://www.sqlservercentral.com/articles/Tally+Table/72993/2. Use the tokens to further split them into one or two tokens based on the presence of the keyword "TO"3. use the one or two tokens thus obtained together with a numbers table to generate the final result.It takes a bit of time, but it can be done. However, data such as this is notoriously unreliable because if the string does not follow the exact pattern of separators (& and TO), the scheme will fail or will generate incorrect results. |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 08:08:12
|
I agree this is not the best way to have the data, but I have to live with that existing situation like you assumed ! :-)I believe you have the right approach to solve this problem. I will do my home work and get back here to share my results.Thanks James! |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-05 : 08:10:19
|
You are welcome. If you run into problems, post back with the code you have. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 11:25:11
|
is the Status column static (always the Status 'Working' and a few other status list) or dynamic (unknown)<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 11:47:05
|
The status is dynamic. One other thing I am thinking of is to have the data as follow instead if easier...Status|RangeWorking|00-0,00-1,01-0,01-1,02-0,02-1,05-1,06-0,06-1,40-1Regards! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 11:56:44
|
what are the list of statuses<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 11:58:11
|
maybe leverage xml by replacing the delimiters and casting them into an xml stringdeclare @bootleg table( bootlegid int identity(1,1), bootleg varchar(max))insert into @bootlegSELECT 'Working|00-0 TO 02-1 & 05-1 TO 06-1 & 40-1'SELECT *, CAST('<r>' + REPLACE(Bobo, 'TO', '</r><r>') + '</r>' AS XML) FROM (select *, CAST('<r>' + REPLACE([Range], '&', '</r><r>') + '</r>' AS varchar(max)) Bobofrom (select bootlegid, SUBSTRING(bootleg,0,charindex('|',bootleg,0)) as [Range1], replace(bootleg,'Working|','') as [Range] from @bootleg ) a) b SELECT bootlegid, bootleg, CAST('<r>' + REPLACE(bootleg, '&', '</r><r>') + '</r>' AS XML) Authors FROM @bootleg <><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 13:34:49
|
Hi Yosiasz,The string 'working' belongs to the column name Status and the string '00-0 TO 02-1 & 05-1 TO 06-1 & 40-1' belongs to the column name Range.The results from your code are not what I wish to have.Results I wish to get from the actual data would be like:Status|RangeWorking|00-0,00-1,01-0,01-1,02-0,02-1,05-1,06-0,06-1,40-1Thanks! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 13:56:25
|
[code]declare @bootleg table( bootlegid int identity(1,1), bootleg varchar(max))insert into @bootlegSELECT 'Working|00-0 TO 02-1 & 05-1 TO 06-1 & 40-1'SELECT *, CAST('<infodemers><status status="' + [Status] + '">' + REPLACE(Bobo, 'TO', '</range><range>') + '</status></infodemers>' AS XML) FROM (select *, CAST('<range>' + REPLACE([Range], '&', '</range><range>') + '</range>' AS varchar(max)) Bobofrom (select bootlegid, SUBSTRING(bootleg,0,charindex('|',bootleg,0)) as [Status], replace(bootleg,'Working|','') as [Range] from @bootleg ) a) b<infodemers> <status status="Working"> <range>00-0 </range> <range> 02-1 </range> <range> 05-1 </range> <range> 06-1 </range> <range> 40-1</range> </status></infodemers>[/code]<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 13:59:21
|
Hi,In the results, you are missing the values between 00-0 TO 02-1 and 05-1 TO 06-1 which are 00-0,00-1,01-0,01-1,02-0,02-1,05-1,06-0,06-1Regards! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 13:59:54
|
that is for your homework :)<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 14:13:53
|
:-) Lol.... That is a way to tell things |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 14:21:26
|
:) well i was just trying to demonstrate how you can leverage xml to do parsing. as you can see the final xml result you are half way there. question is the trailing value for the ranges always gonna be 1 or 0?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 14:33:47
|
That is right! |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 14:38:01
|
Status|RangeWorking|00-0Working|00-1Working|01-0Working|01-1Working|02-0Working|02-1Working|05-1Working|06-0Working|06-1Working|40-1so should there be a 05-0 and 40-0?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
infodemers
Posting Yak Master
183 Posts |
Posted - 2013-02-05 : 15:05:29
|
No :-) |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2013-02-05 : 16:13:39
|
why not. your design is very arbitrary. what is your requirement for what does have and does not have trailing 0 and 1. you need to re-examine your design1. Need to create a Status_List table StatusID, StatusDescr, StatusSort, StatusActive bit and audit columns2. Create a table WorkStatus table WorkStatusid, StatusID, Range and another id foreign key that ties together ranges in range column you need data as 00-1 and nothing moreconsider redesign/<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|