SQL Substring using two charindexes problem

Question

I have the following query:

DECLARE @str VARCHAR(500) 
SET @str = 'Barcode: 22037 CaseSize: 1 Qty: 3'

SELECT RTRIM(LTRIM(
       SUBSTRING(@str, CharIndex('CaseSize: ', @str) + 10, CHARINDEX('Qty:', @str))
       ))

The following results in: '1 Qty: 3'

I would like to be able to only select the Case Size number, which is one in this case.

What is the simplest method of accomplishing this task? A colleague solved the issue by creating a start and end variable and then using them in the substring:

Declare @start INT , @end INT 
SET @start = CharIndex('CaseSize: ', @str) + 10 
SET @end = CHARINDEX('Qty:', @str ) 

SELECT CONVERT(INT, LTRIM(RTRIM(SUBSTRING(@str, @start, @end - @start))))

Since I'm doing this in a select statement, what would be the easiest way? IE. Why does my select statement above fail?

Answer 1

The problem is that the substring (at least in MSSQL and apparently MySQL) takes the length of the substring as the third argument, not the ending index. Your coworker's example works because it does @end - @start which returns the length that you want.

Your example would work correctly if modified to be:

DECLARE @str VARCHAR(500) 
SET @str = 'Barcode: 22037 CaseSize: 1 Qty: 3'
SELECT RTRIM(LTRIM(SUBSTRING(@str, 
                             CharIndex('CaseSize: ', @str) + 10, 
                             (CHARINDEX('Qty:', @str )-(CharIndex('CaseSize: ', @str) + 10)))))

http://msdn.microsoft.com/en-us/library/ms187748.aspx?ppud=4 http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function%5Fsubstring

Answer 2

SUBSTRING takes a start and a length, not a start and an end. Your select statement above is missing the - @end that your colleague had.