CODEWITHSUNDEEP
sqlsql-serverstring
user99143
user99143

Reputation: 111

SQL Substring using two charindexes problem

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?

Upvotes: 0

Views: 4246

Answers (2)

Keith Randall
Keith Randall

Reputation: 23265

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.

Upvotes: 1

sgriffinusa
sgriffinusa

Reputation: 4221

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_substring

Upvotes: 3

Related Questions