select a particular string from a semi-colon delimited list

Question

I want to extract a string which has semi-colon as a delimiter. I tried using Substring, Charindex and Left function. But I'm not able to get the desired result. Below is my select statement. Output result must be "Unsure how to perform task. Meter read 10 in office before testing". Thanks

Declare @string Varchar(max)='Sampling:45;Traveling:30;CalibratedNo;uncalibratedReason:: ' +
                             'Unsure how to perform task.  Meter read 10 in office before ' +
                             'testing.;pH1:6.5;pH2:6.5;Dis.Oxygen1:7.4'

Select SubString(@string, (CHARINDEX('uncalibratedReason:', @string, 0) + 19), 
  (CharIndex('uncalibratedReason:', LEFT(@string, (LEN(@string) -
  (CharIndex(';', @string, 0)))), 0) - 0)) As New

Shnugo · Answer 1 · 2016-08-16T15:27:19.330

Try it like this:

Declare @string Varchar(max) = 'Sampling:45;Traveling:30;CalibratedNo;uncalibratedReason:Unsure how to perform task.  Meter read 10 in office before testing.;pH1:6.5;pH2:6.5;Dis.Oxygen1:7.4';

SELECT CAST('<x>' + REPLACE(@string,';','</x><x>') + '</x>' AS XML).value('x[4]','nvarchar(max)')

The result is:

uncalibratedReason:Unsure how to perform task.  Meter read 10 in office before testing.

You can take away the leading uncalibratedReason: simply with SUBSTRING and CHARINDEX looking for : if you need this.

UPDATE

Here is the full code:

DECLARE @result NVARCHAR(MAX)=
(SELECT CAST('<x>' + REPLACE(@string,';','</x><x>') + '</x>' AS XML).value('x[4]','nvarchar(max)'));

SELECT SUBSTRING(@result,CHARINDEX(':',@result)+1,10000)

UPDATE 2: Find position by starting string

DECLARE @result NVARCHAR(MAX)=
(SELECT CAST('<x>' + REPLACE(@string,';','</x><x>') + '</x>' AS XML).value('(x[substring(.,1,string-length("uncalibratedReason:")) eq "uncalibratedReason:"])[1]','nvarchar(max)'));

SELECT SUBSTRING(@result,CHARINDEX(':',@result)+1,10000)

UPDATE 3 The ultimative solution :-)

Declare @string Varchar(max) = 'Sampling:45;Traveling:30;CalibratedNo;uncalibratedReason:Unsure how to perform task.  Meter read 10 in office before testing.;pH1:6.5;pH2:6.5;Dis.Oxygen1:7.4';


WITH Casted(ThePart) AS
(
    SELECT Node.value('.','nvarchar(max)')
    FROM
    (
    SELECT CAST('<x>' + REPLACE(@string,';','</x><x>') + '</x>' AS XML)
    ) AS tbl(AsXML)
    CROSS APPLY AsXML.nodes('/x') AS The(Node)
)
,Splitted(SpecificPart) AS
(
    SELECT CAST('<x>' + REPLACE(ThePart,':','</x><x>') + '</x>'  AS XML) 
    FROM Casted
)
SELECT SpecificPart.value('x[1]','nvarchar(max)') AS Caption
      ,SpecificPart.value('x[2]','nvarchar(max)') AS Data
FROM Splitted

The result

Caption             Data
CalibratedNo        NULL
Dis.Oxygen1         7.4
pH1                 6.5
pH2                 6.5
Sampling            45
Traveling           30
uncalibratedReason  Unsure how to perform task.  Meter read 10 in office before testing.

Appreciate your response. What if the field is not always at 4 ? That string might be in 3'rd field or 7'th field. — wwidhoo, Aug 16 '16 at 15:07
How would you know where to search? Is the string always starting with `uncalibratedReason:`? — Shnugo, Aug 16 '16 at 15:09
Currently i'm only interested to extract the string for uncalibratedReason. But it doesnt always starts on 4'th. Example some strings doesn't have fields such as sampling or Traveling. — wwidhoo, Aug 16 '16 at 15:13
In this case you'd have to dive a bit deeper into `XQuery`. See my update — Shnugo, Aug 16 '16 at 15:21
Thanks, both your solutions worked. It was nice to learn how to segregate the strings using xml. — wwidhoo, Aug 16 '16 at 15:43

John Cappelletti · Accepted Answer · 2016-08-16T15:26:07.690

Shnugo anwser very cool. (UpVote)

However, this UDF Parser returns the sequence and value

Declare @string Varchar(max)='Sampling:45;Traveling:30;CalibratedNo;uncalibratedReason:: ' +
                             'Unsure how to perform task.  Meter read 10 in office before ' +
                             'testing.;pH1:6.5;pH2:6.5;Dis.Oxygen1:7.4'

Select * from [dbo].[udf-Str-Parse](@String,';')
  --Where Key_PS = 5
  --Where Key_Value Like '%:%'
  --Where Key_Value Like 'pH1%'

Returns

Key_PS  Key_Value
1       Sampling:45
2       Traveling:30
3       CalibratedNo
4       uncalibratedReason:: Unsure how to perform task.  Meter read 10 in office before testing.
5       pH1:6.5
6       pH2:6.5
7       Dis.Oxygen1:7.4

The UDF

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1), Key_Value varchar(max))
As
Begin
   Declare @XML xml;Set @XML = Cast('<x>' + Replace(@String,@Delimeter,'</x><x>')+'</x>' as XML)
   Insert Into @ReturnTable Select ltrim(rtrim(String.value('.', 'varchar(max)'))) FROM @XML.nodes('x') as T(String)
   Return 
End

Hi, if you like my answer, you might want to see my `UPDATE 2` :-) And if you want to read about a method to get this mehtod save, even if your string is carrying forbidden characters, you might want to read my answer at the question which is linked as duplicate :-) — Shnugo, Aug 16 '16 at 15:30
@Shnugo Even better. I've got a Love/Hate relationship with SO. Love how I can learn something new and help others. Hate how dumb it makes me feels when I see stuff like that. — John Cappelletti, Aug 16 '16 at 15:36

select a particular string from a semi-colon delimited list

2 Answers2

UPDATE

UPDATE 2: Find position by starting string

UPDATE 3 The ultimative solution :-)