Split string from a string SQL Server 2008

Question

I would like to take value from a string. Please see this example string format: "(Fault Reported: Not Heating - POD - CC - We fit shower 2 years ago - Lime - €124.00 ) ( Job Report: Replaced Element & Stat. €149.00 paid CC 08/17)" I would like to select second parentheses value

 `(Job Report: Replaced Element & Stat. €149.00 paid CC 08/17)`

How could I achieved that? I tried below way but can't get the actual value.

DECLARE @s VARCHAR(max)='(Fault Reported: Not Heating - POD - CC - We fit shower 2 years ago - Lime - €124.00 ) ( Job Report: Replaced Element & Stat. €149.00 paid CC 08/17) '
SELECT PARSENAME( REPLACE(@s,') (','.'),2)

Aaron Bertrand · Accepted Answer · 2020-08-10 17:42:17Z

1

PARSENAME() is not the best choice here because there are other <unknown number> of . characters, which would need to be replaced first and then put back after, which gets messy fast.

How about:

DECLARE @s varchar(max) = '(Fault Reported: Not Heating - POD - CC - We fit '
  + 'shower 2 years ago - Lime - €124.00 ) ( Job Report: Replaced Element '
  + '& Stat. €149.00 paid CC 08/17) ';

SELECT SUBSTRING(@s, CHARINDEX(') (', @s) + 2, 8000);

Result:

( Job Report: Replaced Element & Stat. €149.00 paid CC 08/17)

One example is almost never enough to provide the right solution, though. Lots of edge cases you probably haven't considered yet. The most glaring:

What if there is only one set of parentheses? Do you want NULL, empty string, something else?
What if there are more than two? Do you want everything 2+? Only the 2nd? Only the last? Something else?

edited Aug 10, 2020 at 17:42

answered Aug 10, 2020 at 17:36

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

1 Comment

Letoncse Over a year ago

Thank you so much , its work fine for me. My query like that SELECT SUBSTRING(ProbDesc, CHARINDEX(') (', ProbDesc) + 2, 8000) from servicerecords where SerRecID is not null.

Collectives™ on Stack Overflow

Split string from a string SQL Server 2008

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related