I get an error due to one of the current descriptions.. Also like 79 which has C4 shows 4 but no CNT so its value should not show up.

Thanks for the help, it's appreciated.

SQL 2012 R2

It is an ERP product that has come up through the years.

I fixed it this way

SELECT   ROWID, ID, DESCRIPTION, CASE WHEN DESCRIPTION LIKE '%CNT%' THEN RIGHT(RTRIM(SUBSTRING(DESCRIPTION, 1, PATINDEX('%CNT%', DESCRIPTION) - 1)), PATINDEX('%[^0-9]%',
         LTRIM(REVERSE(SUBSTRING(DESCRIPTION, 1,...

So I figured out not all DESCRIPTION fields have CNT in them so it's returning a null value.  I did a filter description like '%CNT%' then I was able to...

I am getting an error.

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

I tried playing with it...

The data I am looking for to be returned from a select statement is the 2 or 3 digit number to the right on the text ' CNT'

I...

Sorry.. I should have put an example of what I was looking for.

From this : GLP1 SUPPORT 60 CNT/BT I want the number 60 only.  

60 Count...