I need help parsing a string with SQL Query. I have the following type of data, and need to be able to parse out specific values after a specific character:
Example of raw data:
Emp# 89898 & 29393 - terminated
Emp# 00903, 299982, 1220 - contract
I just need to be able to pull the numbers. So output be as followed:
89898 & 29393
00903, 299982, 1220
I tried the following, but seems to pull everything after my pawn sign.
SELECT DEPT_ID, SUBSTRING(NOTE, CHARINDEX('#', NOTE)+1, LEN(NOTE))
So basically any comment is follow by a dash. Would like to pull anything between # and dash sign. I found another example, but this one will not pick it up if the field does not have both characters.
SUBSTRING(NOTE, CHARINDEX('#', NOTE, 1)+1
, CASE WHEN (CHARINDEX('-', NOTE, 0) - CHARINDEX('#', NOTE, 0)) > 0
THEN CHARINDEX('-', NOTE, 0) - CHARINDEX('#', NOTE, 0) - 1