I have a requirement to extrapolate from a SQL String Field our Invoice Number.
My Query is as follows:
SELECT CUSTNMBR,
CAST(DATE1 AS DATE) AS Date,
Action_Promised,
Amount_Promised,
SUBSTRING(
Note_Display_String,
PATINDEX('%REF%', Note_Display_String) + 0,
LEN(Note_Display_String) - PATINDEX('%;REF;%', Note_Display_String) + 0
) AS InvNumber,
Note_Display_String
FROM CN00100
WHERE DATE1 >= '2022-01-01'
AND Action_Promised LIKE '%Dun%';
I need to get the Invoice Number (REFXXXXXX) from the Note_Display_String Field. How can I change my Substring to only Grab REFXXXXXX no matter where in the string it falls? Thanks in advance......
Query returns this:
