Tsql Select All Characters Right of Certain Character

Question

I have a varchar column containing values similar to "1.2.3 Technical Report - Study/Services, Material and Process Test Reports", and need SELECT to return "Study/Services, Material and Process Test Reports", meaning everything to the right of the hyphen and space.

This tsql...

select right(COLUMN, charindex(' - ', COLUMN)) from MYTABLE where PK_ID = 123;

Returns... "nd Process Test Reports"

Suggestions?

1

use substring instead of right – Clay Mar 12 '19 at 18:08

score 1 · Answer 1 · answered Mar 12 '19 at 18:11

1

Use substring() :

select substring(col, charindex('- ', col) + 2, len(col))
from MYTABLE 
where PK_ID = 123;

answered Mar 12 '19 at 18:11

Yogesh Sharma

49,870
5
26
52

score 0 · Answer 2 · answered Mar 12 '19 at 18:19

As an alternative approach, instead of returning all the characters to the "right" of the pattern, you could "remove" all the characters up to and including them

SELECT STUFF(Col, 1, CHARINDEX(' - ',col) +3, '') AS NewCol
FROM MYTABLE
WHERE PK_ID = 123;

If your column may not have the pattern ' - ' then you could do:

STUFF(Col, 1, ISNULL(NULLIF(CHARINDEX(' - ',col),0) +3),0), '')

Tsql Select All Characters Right of Certain Character

2 Answers2