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
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With