SQL Field = List String

[SQLMeToo]

I have a table with a char field - the values are for this field for each record consist of a list string. E.g.&quot;data 1;data 2;data 3; data 4&quot;.<br><br>I want to select the field and insert each list value into a new table. Can SQL perform this function.<br>Thanks

 

[ssingh]

I don't thing only SQL will do that. You need to use some scalar functions like string manipultations to accomplish that.<br><br>Does this answer your question ?<br>Thanx<br>Siddhartha Singh<br><A HREF="mailto:ssingh@aztecsoft.com">ssingh@aztecsoft.com</A>

 

[calahans]

What RDBMS are you using - im thiking that this may be accomplishable with a stored procedure with some form of loop based on a SUBSTRING() function not evaluating to 0.<br><br>However this may be better suited to a programming language.<br> <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br> Nobody told me that the price of shares could fall... :-(

 

[carp]

ssingh and calahans are both right.&nbsp;&nbsp;SQL won't do something like this, but it is quite simple with a stored procedure.&nbsp;&nbsp;Let us know which RDBMS you are using and somebody will be able to show you the code.

 

[SQLMeToo]

I have had a few responses to my question. Most people suggest: Charindex (to detect position of separator) and substring&nbsp;&nbsp;(to take out needed subfield) functions in cycle.<br><br>I am using MS SQLServer - sample code would be great!

 

[cdukes]

Here is a piece of code that you might like to start with,<br>I have not tested or run it (just typed into notepad !!) , but it might give you a <br>starting position. You might want to add some print a<br>statements in order to see what it is doing. The Position integers <br>might also need a bit of tweeking.<br><br>DECLARE @StringToProcess VARCHAR(255)<br>DECLARE @SemiPosition INTEGER<br>DECLARE @StartPosition INTEGER<br>DECLARE @NewStringPiece VARCHAR(50)<br><br>DECLARE myCursor CURSOR for <br> Select MyField from Table<br><br>OPEN myCursor <br><br>FETCH NEXT FROM myCursor INTO<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@StringToProcess<br><br>WHILE @@FETCH_STATUS = 0<br>BEGIN<br><br> SELECt @StartPosition = 0<br><br> -- Search for the first semi colon<br> SELECT @SemiPosition = CHARINDEX(';',@StringToProcess, @StartPosition)<br> <br> -- NOTE cant remember what CHARINDEX returns if string is not found<br> -- might be NULL<br> While @SemiPosition &lt;&gt; 0<br> BEGIN<br><br> -- Get the piece of string<br> SELECT @NewStringPiece = SUBSTRING(@StringToProcess, @StartPosition, @SemiPosition - @StartPosition)<br><br> -- DO something with new string<br> INSERT INTO NewTable <br> Values (@NewStringPiece )<br><br> -- increment startposition to next piece <br> -- of string<br> SELECT @StartPosition = SemiPosition + 1<br><br> -- Get Next semi colon<br> SELECT @SemiPosition = CHARINDEX(';', @StringToProcess, @StartPosition)<br> END<br><br><br> FETCH NEXT FROM myCursor INTO<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@StringToProcess<br>END<br><br>CLOSE myCursor <br>DEALLOCATE myCursor <br><br>Let me know if this helps.