SQL - How to extract a list of strings between a delimiter?

Question

In SQL Server, if I have an example string of 'abc||DATE||Lorem ipsum||NUMBER||dolor sit amet, elit.||CONTACT NAME||:Nullam et odio laoreet', any ideas on how I would extract all of the strings between my '||' delimiter? My expected result set would be:

DATE
NUMBER
CONTACT NAME

I would also be able to work with a single, comma delimited result of: DATE,NUMBER,CONTACT NAME. Also, there could be any number of these substrings that I would need to extract. My sample above only has three. Thanks for any help you can provide!

http://stackoverflow.com/questions/314824/t-sql-opposite-to-string-concatenation-how-to-split-string-into-multiple-reco — Chris Wissmach, Jul 19 '16 at 19:59
Possible duplicate of [How do I split a string so I can access item x?](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Rubens Farias, Jul 19 '16 at 20:26

score 0 · Answer 1 · answered Jul 19 '16 at 20:10

0

Try this SQL Server function I created. It's .net so you would have to enable CLR functions or re-create this in SQL Server.

<SqlFunction(DataAccess:=DataAccessKind.Read)> _
    Public Shared Function Split(Text As String, delimiter As String, returnZeroBasedIndex As Integer) As String
        Dim s() As String = VB.Split(Text, delimiter)
        If returnZeroBasedIndex <= s.Length - 1 Then
            Return s(returnZeroBasedIndex)
        Else
            Return ""
        End If
    End Function

answered Jul 19 '16 at 20:10

Michael Z.

1,453
1
15
21

Why is this down-voted? I use this everyday and it's very useful and very easy to use. – Michael Z. Jul 19 '16 at 20:16
I'm not sure why the downvote. I will research on how I could use this in my scenario. Thanks for your help. – tremonti93 Jul 19 '16 at 20:26
I checked the link in the comment to your post and it contains SQL Server implementations of this. The CLR is faster though and easier to understand. – Michael Z. Jul 19 '16 at 20:27

SQL - How to extract a list of strings between a delimiter?

1 Answers1