Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split string value

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|Range
Working|00-0 TO 02-1 & 05-1 TO 06-1 & 40-1
I 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|Range
Working|00-0
Working|00-1
Working|01-0
Working|01-1
Working|02-0
Working|02-1
Working|05-1
Working|06-0
Working|06-1
Working|40-1

Any 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.
Go to Top of Page

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!
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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|Range
Working|00-0,00-1,01-0,01-1,02-0,02-1,05-1,06-0,06-1,40-1

Regards!
Go to Top of Page

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
Go to Top of Page

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 string



declare @bootleg table( bootlegid int identity(1,1), bootleg varchar(max))

insert into @bootleg
SELECT '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)) Bobo
from (
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
Go to Top of Page

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|Range
Working|00-0,00-1,01-0,01-1,02-0,02-1,05-1,06-0,06-1,40-1

Thanks!
Go to Top of Page

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 @bootleg
SELECT '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)) Bobo
from (
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
Go to Top of Page

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-1

Regards!
Go to Top of Page

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
Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2013-02-05 : 14:13:53
:-) Lol.... That is a way to tell things
Go to Top of Page

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
Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2013-02-05 : 14:33:47
That is right!
Go to Top of Page

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-02-05 : 14:38:01
Status|Range
Working|00-0
Working|00-1
Working|01-0
Working|01-1
Working|02-0
Working|02-1
Working|05-1
Working|06-0
Working|06-1
Working|40-1

so should there be a 05-0 and 40-0?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page

infodemers
Posting Yak Master

183 Posts

Posted - 2013-02-05 : 15:05:29
No :-)
Go to Top of Page

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 design

1. Need to create a Status_List table
StatusID, StatusDescr, StatusSort, StatusActive bit and audit columns
2. 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 more

consider redesign/

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page
   

- Advertisement -