SQL parse delimited string

Question

I can't find a good way to do this and because of some restrictions it has to be a coded without the use of variables but I can call a function. Anyway, I need to return a result set from a Select query and one of the rows is a pipe delimited string.

So, it will return something like:

id| Name      | Message |
---------------------
1 | Some Name | 'Here is my | delimited | message'

And I need it to be

id| Name      | Message1     | Message2    | Message3
------------------------------------------------------
1 | Some Name | 'Here is my' | 'delimited' | 'message'

I was thinking of something like Parsename('','|',1), where you can pass in the delimiter instead of it always being a period but I don't know the best way to accomplish that.

EDIT: I've tried variations of this but of course it is very confusing. There could be 4 or more |

SELECT Field1, 
Field2 AS Originalvalue,
--1
SUBSTRING(Field2,0,CHARINDEX('|',Field2)) AS Msg1,
--2
LEFT(SUBSTRING(Field2,CHARINDEX('|',Field2)+1 ,LEN(Field2)),CHARINDEX('|',SUBSTRING(Field2,CHARINDEX('|',Field2)+1 ,LEN(Field2)))-1)
AS ExtractedValue
FROM Table1 T1 JOIN Table2 T2
ON T1.Id = T2.Id
WHERE T1.Id = 12

Hmm, do people not answer questions on this site? It seems to be a lot of 'go look at google'. I've been looking for over an hour now and I haven't found exactly what I'm looking for. — user204588
– user204588, Commented Oct 28, 2011 at 1:56
@user204588 You're not going to get a great answer (if you get one at all) because your design is extremely poor. — Phil
– Phil, Commented Oct 28, 2011 at 2:02

Pramod Pallath Vasudevan · Accepted Answer · 2011-10-28 02:08:34Z

8

You can write a sql function as follows.

create Function dbo.fn_Parsename(@Message Varchar(1000), @delimiter char(1), @index int )
Returns Varchar(1000)
As
Begin
    Declare
        @curIndex int = 0,
        @pos int = 1,
        @prevPos int = 0,
        @result varchar(1000)

    while @pos > 0
    Begin

        set @pos =  CHARINDEX(@delimiter, @Message, @prevPos);

        if(@pos > 0)
        begin-- get the chars between the prev position to next delimiter pos
            set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos)
        end
        else
        begin--get last delim message
            set @result = SUBSTRING(@message, @prevPos, LEN(@message))
        end

        if(@index = @curIndex)
        begin
            return @result
        end

        set @prevPos = @pos + 1
        set @curIndex = @curIndex + 1;

    end
    return ''--not found
End

And you can call it as below:

select dbo.fn_Parsename('Here is my | delimited | message','|', 0)
select dbo.fn_Parsename('Here is my | delimited | message','|', 1)
select dbo.fn_Parsename('Here is my | delimited | message','|', 2)

answered Oct 28, 2011 at 2:08

Pramod Pallath Vasudevan

80911 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nick Knauer Over a year ago

If your message is a column, would the formula still stay exactly the same? For example, would I write exactly: @delimitor, etc.? Also can I call the function for a full column and do something like this? "select *, dbo.fn_Parsename(Message,'|', 0), dbo.fn_Parsename(Message,'|', 1) FROM table1

Collectives™ on Stack Overflow

SQL parse delimited string

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related