[SQL] Split Field Strings at ;

[SterAllures]

Hey CC'ers,

Once again I got a question. I'm still working with reporting services.

My problem is that i have a table in a database and in that table I've got a row with a column name Results.
Well in my Result field I've the following information yes;no;no;yes;yes
Now I want to split those Results into seperate Rows.

So that I will have the following:

Name | Answer
-----------------
Result1 | yes
Result2 | no
Result3 | no
Result4 | yes
Result5 | yes

Instead of

Name | Answer
-----------------
Result1 | yes;no;no;yes;yes


I hope someone understand my problem and has a solution for this matter .


Thanks In Advance!!
//SterAllures

[SterAllures]

I read a few articles and I think it has somenthing to do with SUBSTRING to select the part you want to trim. And maybe somenthing like CHARINDEX to find the value behind ;. So I hope somebody can help me how to write the correct query

[BlaineSch]

Substring is used for strings not databases. I do not think there is a way to make it give you results that look like this:

Name | Answer
-----------------
Result1 | yes;no;no;yes;yes

Since result1 isnt true on all of them - maybe you could show us a bit more details.

[SterAllures]

Ya I Think I Explained It Kinda Wrong But I Got A Part Of The Answer Already Here It Is!

Table: SPLITSTRINGS

ColumnNames ->       Results	|   Result1   |   Result2   |
		    ----------------------------------------------------
Values->	     Ja;nee;no	|NULL          | NULL          |

Query:

UPDATE SPLITSTRINGS
Set Result1 = SUBSTRING(Results, CHARINDEX(‘;’, Results) +1, LEN(Results))
WHERE Result1 LIKE ‘%;%’

The Result

ColumnNames ->       Results	|   Result1   |   Result2   |
		    ----------------------------------------------------
Values->	     Ja;nee;no	|nee;no      | NULL          |

So Now I But The answers from column Results into seperate Columns.

If I Now Edit the query A Bit I will get the following:

ColumnNames ->       Results	|   Result1   |   Result2   |
		    ----------------------------------------------------
Values->	     Ja;nee;no	|nee;no      | no         |

But my Question Now Is. How can I edit the query so that I will only get the last value of the Results Columns into a seperate one?

[Orjan]

You want it automatically just take the last one? hmm that would absolutely be alot harder.
is it always the same amount of results? if so, you could to it as you do now recursively for the fifth.

[SterAllures]

Ya I think I got it kind of wrong again .

I want to split a field which is the same amount every time (5 answers) yes or no seperated by ;.

So I would want somenthing like.

if you have a table with name, adult, male, work, drunk, sports
(somenthing like that)

now I want to seperate the already insert data into those fields so it has to split in at those fields so like:

ColumnNames ->       John	|   adult   |   male   |    work      |  drunk   |   sports
		    ---------------------------------------------------------------------
Values->	     	                |      no      |     yes     |    yes       |     no     |   yes

I really hope you understand it !

[Orjan]

Well, why not just use your query, but use ot bothways from the start and from the end.
put the part before the ; in one field and the thing after the ; in another field, rerun the query and so on, this is best to do on a temporary table, maybe within a stored procedure.

[SterAllures]

mm I will look into that stored procedure and try to edit the query, Thanks for your time , hope it works than . is there a special subject of stored procedure I could look into?

[Orjan]

A stored procedure is more or less a stored set of sql queries, with some loops, conditions and variables to help.
is this something you need to do often, or seldom? wher ewill you use the results from this? in another programming language? then it will probably be easier to do this division inside that language instead of in SQL.

[SterAllures]

well I use it in reporting services and that's used with VB.NET but I think its easier to do it in SQL cause I will need it more and if it has top be coded in VB.NET you have to edit teh code everytime to make it suitable for the software and for SQL you don't have to do that and just add the function to every database so I think it is easier that way