Parse value from column and insert into new table in T-Sql

Question

I have a column that I need to parse and insert into a new table. I've come extremely close to getting the data I need but I can't seem to get the syntax correct. Here is the format of the data I need to parse:

Packing Slip #195, UID = Pkg-15094-195

Packing Slip #112, UID = Pkg-41251-241

I just need the packing slip number. It's not always going to be 2 characters of course.

Seems relatively simple, just get the difference of the index of the characters [#] and [,] Then start at the index of [#] + 1 for a length of [,] - [#] - 1. It's that extra subtraction that is screwing up my syntax:

SELECT substring(IMG_FILE_DESCRIPTION, 
    CHARINDEX('#', IMG_FILE_DESCRIPTION) + 1,
    CHARINDEX(',', IMG_FILE_DESCRIPTION) - CHARINDEX('#', IMG_FILE_DESCRIPTION)
)
AS PKL
FROM MASTER_SCAN_IMAGE
where IMG_SCT_PKEY = '21'

That works, but gives me one too many. If I attempt to add another subtraction like so I get a syntax error:

SELECT substring(IMG_FILE_DESCRIPTION, 
    CHARINDEX('#', IMG_FILE_DESCRIPTION) + 1,
    (CHARINDEX(',', IMG_FILE_DESCRIPTION) - CHARINDEX('#', IMG_FILE_DESCRIPTION)) -1
)
AS PKL
FROM MASTER_SCAN_IMAGE
where IMG_SCT_PKEY = '21'

Msg 537, Level 16, State 2, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function.

So I figure I should wrap up the length value into a single variable but it's also creating a syntax error:

SELECT *
FROM MASTER_SCAN_IMAGE
DECLARE @length int = CHARINDEX(',', IMG_FILE_DESCRIPTION) - CHARINDEX('#', IMG_FILE_DESCRIPTION);

Msg 207, Level 16, State 1, Line 3
Invalid column name 'IMG_FILE_DESCRIPTION'.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'IMG_FILE_DESCRIPTION'.

In fact I can't seem to assign a variable at all using CHARINDEX, I always end up with the same "invalid column name" error.

DECLARE @length bigint = CHARINDEX(',', IMG_FILE_DESCRIPTION);

Msg 207, Level 16, State 1, Line 3
Invalid column name 'IMG_FILE_DESCRIPTION'.

It's the same whether I use int or bigint which are the two return types that charindex can send.

Answer 1

Use this:

declare @column varchar(200) = 'Packing Slip #195, UID = Pkg-15094-195'


select RIGHT(LEFT(@column, CHARINDEX(',', @column)-1), CHARINDEX('#', REVERSE(LEFT(@column, CHARINDEX(',', @column)-1)))-1) 

Run the code here.