Separate String with separator #@#

Question

Hi All,

The string below I have to call three times using sql. String: JAN#@#PIET#@#HENK

The first call shoudl return: JAN The second call: PIET The third: HENK

So we could use the #@# as separator but it could be that the string is: JAN#@#PIET only. Still all three calls will be done where call 1 returns: JAN call 4 returns: PIET cal three return: <>

Same could happen for string JAN only.

I hope this explanation is sufficient for someone to help me with this case.

Thanks in advance.

Regards, Ryni

Answer 1

if you're using sql 2005, adapt this post to your needs:

Split Funcin - SQL 2005

Answer 2

every possible SQL Server split string method with detailed pro and cons:

http://www.sommarskog.se/arrays-in-sql.html

Answer 3

It sounds to me like you are asking for a split function that maintains state like an enumerator. You don't actually want that. That could be very bad potentially.

I'd recommend a split string function (in sql server, they're called table valued functions). If you do that, worst case scenario is you have to loop over the table result. In any event, you can find the function here: http://dpatrickcaldwell.blogspot.com/2008/08/table-valued-function-to-split-strings.html

Your usage would look like this:

SELECT *
FROM dbo.SplitString('JAN#@#PIET#@#HENK', '#@#')
-- PartId      Part
-- ----------- --------
-- 1           JAN
-- 2           PIET
-- 3           HENK

SELECT *
FROM dbo.SplitString('JAN#@#PIET#@#HENK', '#@#')
WHERE PartId = 2
-- PartId      Part
-- ----------- --------
-- 2           PIET