CODEWITHSUNDEEP
sqlsql-servert-sqlsubstringpatindex
Wilmar
Wilmar

Reputation: 568

Extract substring from SQL Server column

I am trying to figure out how to extract a substring from a column in SQL Server. I very much would like to be able to do it directly in SQL if at all possible. The content of my column holds the responses of a web form and looks like this:

"a:27:{s:5:\"FieldX\";s:22:\"Response to FieldX\";s:16:\"WhatProductDoYouWant\";s:31:\"SomeProduct\";s:16:\"FieldY\";s:4:\"Response to FieldY\"}

In the previous example the form has three fields with their respective responses as:

FieldName                  Response
FieldX                     Response to FieldX
WhatProductDoYouWant       SomeProduct
FieldY                     Response to FieldY

I need to extract the answer to WhatProductDoYouWant, that is, I need SomeProduct.

Constraints:

For a full example, let's say I have the following table in SQL Server table:

CREATE TABLE WebFormData 
(
    FormID int,
    Responses varchar(MAX)
);

INSERT INTO WebFormData (FormID, Responses)
VALUES (1, 'a:27:{s:5:\"FieldX\";s:22:\"Response to FieldX\";s:16:\"WhatProductDoYouWant\";s:31:\"SomeProduct\";s:16:\"FieldY\";s:4:\"Response to FieldY\"}');

INSERT INTO WebFormData (FormID, Responses)
VALUES (2, 'a:27:{s:5:\"FieldX\";s:22:\"Response to FieldX\";a:27:{s:7:\"FieldX2\";s:27:\"Response to FieldX2\";s:16:\"WhatProductDoYouWant\";s:31:\"SomeOtherProduct\";s:16:\"FieldZ\";s:4:\"Response to FieldZ\";s:16:\"FieldY\";s:4:\"Response to FieldY\"}');

I would like to have a SQL query like:

SELECT FormID, someExpression AS Products 
FROM WebFormData

And I would expect to have as results:

1, SomeProduct
2, SomeOtherProduct

I have been able to identify the index of the initial character I am looking for but I have no idea how to determine the length of the substring:

SELECT 
    FormID, 
    SUBSTRING(Responses, CHARINDEX('WhatProductDoYouWant', Responses) + 30, 20) AS Products
FROM 
    WebFormData

(The 20 in the length parameter of the substring function is just a random number for demonstration purposes)

The query returns:

FormID,Products
1, SomeProduct\";s:16:\
2, SomeOtherProduct\";s

Any help would be appreciated. Please let me know if clarification is required.

Upvotes: 0

Views: 87

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81930

Perhaps more than you are looking for.

If you want to parse the entire string

Example

;with cte as (
select FormID 
      ,B.RetSeq
      ,RetVal = replace(B.RetVal,'\"','')
      ,grp = sum(retSeq % 2) over (partition by FormID order by RetSEQ)
      ,col = retSeq % 2
 From WebFormData A
 Cross Apply [dbo].[tvf-Str-Extract-JSON](replace(Responses,'}',';'),':',';') B
)
Select FormID
      ,Seq      = Grp
      ,Question = max(case when col=1 then RetVal end)
      ,Response = max(case when col=0 then RetVal end)
  From cte
  Group By FormID,Grp
  Order By FormID,Grp

Results

enter image description here

The Helper Function if Interested

CREATE FUNCTION [dbo].[tvf-Str-Extract-JSON] (@String nvarchar(max),@Delim1 nvarchar(100),@Delim2 nvarchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = [Key]+1
                  ,RetVal = Value
             From  OpenJSON( N'["'+replace(string_escape(@String,'json'),@Delim1,'","')+N'"]' )

          ) C1
    Where charindex(@Delim2,RetVal)>1

)

enter image description here

Upvotes: 1

Stu
Stu

Reputation: 32579

You can simply chain cross apply with a values clause to find your start and length positions, and use nullif to handle where the text does not exist:

select Substring(Responses, NullIf(p1,0) + 30, p2-(NullIf(p1,0) + 30)) Products
from WebFormData
cross apply (values(CHARINDEX('WhatProductDoYouWant', Responses)))x(p1) 
cross apply (values(CHARINDEX('\"', Responses, p1 + 30 )))y(p2);

Upvotes: 4

Related Questions