Have Geek Brain

Separate line with delimiter # @ #

The next line I have to call three times using sql. Line: JAN # @ # PIET # @ # HENK

First call shoudl return: JAN Second call: PIET Third: HENK

So we could use # @ # as a separator, but it could be that the line: JAN # @ # PIET only. All three calls will be made where call 1 returns: JAN call 4 returns: PIET three returns: <>

The same can only happen for the JAN string.

Hope this explanation is enough for someone to help me in this case.

Thanks in advance.

Regards, Ryni

0


a source to share


3 answers


if you are using sql 2005 adapt this post to your needs:



Split Funcin - SQL 2005

+3


a source


It looks to me like you are asking for a split function that maintains state like an enumerator. You really don't want that. This can be really bad.

I would recommend the string splitting function (in sql server they are called table functions). If you do this, in the worst case you will have to iterate over the result of the table. Anyway, 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
+2


a source


every possible SQL Server string splitting method with detailed pros and cons:

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

+1


a source






More articles:


All Articles