SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split string value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 02/04/2013 :  21:04:36  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/05/2013 :  07:56:15  Show Profile  Reply with Quote
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

Canada
181 Posts

Posted - 02/05/2013 :  08:08:12  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3741 Posts

Posted - 02/05/2013 :  08:10:19  Show Profile  Reply with Quote
You are welcome. If you run into problems, post back with the code you have.
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  11:25:11  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Canada
181 Posts

Posted - 02/05/2013 :  11:47:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  11:56:44  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  11:58:11  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Edited by - yosiasz on 02/05/2013 11:58:53
Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 02/05/2013 :  13:34:49  Show Profile  Reply with Quote
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!

Edited by - infodemers on 02/05/2013 13:37:37
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  13:56:25  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote

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>


<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Edited by - yosiasz on 02/05/2013 13:59:20
Go to Top of Page

infodemers
Posting Yak Master

Canada
181 Posts

Posted - 02/05/2013 :  13:59:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  13:59:54  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Canada
181 Posts

Posted - 02/05/2013 :  14:13:53  Show Profile  Reply with Quote
:-) Lol.... That is a way to tell things
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  14:21:26  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
:) 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

Canada
181 Posts

Posted - 02/05/2013 :  14:33:47  Show Profile  Reply with Quote
That is right!
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  14:38:01  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

Canada
181 Posts

Posted - 02/05/2013 :  15:05:29  Show Profile  Reply with Quote
No :-)
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 02/05/2013 :  16:13:39  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000