Search code examples
sqlsql-servert-sqlsubstringpatindex

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:

  • I do not know how many fields there are before or after the field I am looking for, it is a dynamic form.
  • The answer to the field is dynamic, meaning I do not know how many characters I need to account for, it could be anything.

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.

Solution

  • 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);