CODEWITHSUNDEEP
sql-server-2008
Letoncse
Letoncse

Reputation: 722

Split string from a string SQL Server 2008

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)

Upvotes: 0

Views: 22

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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?

Upvotes: 1

Related Questions