Parsing a string result from a query

Question

My sql server 2008 query returns a very long strings such as:

GET /maker/www/jsp/AutoForm.do?&userid=0asdasdsadasd&dest=Statusasdasdas&url=https%3A%2F%2Femea.salesforce.com%2Fservices%2FSoap%2Fu%2F7.0%2F00D300asdasdasar&id=0062000000KZHg8&getid=WS00D3000asdasdasd&menu=asdasd&if=prings&refreshopp=0 HTTP/1.1

I know I can do things like left(columnname, 10) to return the first 10 characters, but what I am really interested in is the part

getid=WS00D3000asdasdasd

More specifically, I just want the value getid is equal to e.g.

WS00D3000asdasdasd

Any tips?

Answer 1

DECLARE @s VARCHAR(8000);

SET @s = 'GET ...whatever...KZHg8&getid=WS00D3000asdasdasd&menu=...whatever...';

SELECT SUBSTRING(s, 1, COALESCE(NULLIF(CHARINDEX('&', s)-1, -1), 8000))
  FROM (SELECT s = SUBSTRING(@s, CHARINDEX('getid=', @s)+6, 8000)) AS x;

This will also handle the case where getid is the last name/value pair.

CHARINDEX: http://msdn.microsoft.com/en-us/library/ms186323%28SQL.105%29.aspx

SUBSTRING: http://msdn.microsoft.com/en-us/library/ms187748%28SQL.105%29.aspx

Answer 2

Play with this:

DECLARE @MyString as NVARCHAR(max) = 'GET /maker/www/jsp/AutoForm.do?&userid=0asdasdsadasd&dest=Statusasdasdas&url=https%3A%2F%2Femea.salesforce.com%2Fservices%2FSoap%2Fu%2F7.0%2F00D300asdasdasar&id=0062000000KZHg8&getid=WS00D3000asdasdasd&menu=asdasd&if=prings&refreshopp=0 HTTP/1.1',
@MySubstring as NVARCHAR(max) = '',
@StartString as NVARCHAR(max) = '&getid=',
@EndString as NVARCHAR(max) = '&menu=',
@SubStringFrom as INT,
@SubStringFor as INT

-- find the starting position
SET @SubStringFrom = CHARINDEX(@StartString, @MyString) + LEN(@StartString)

-- find the offset
SET @SubStringFor = CHARINDEX(@EndString, @MyString) - CHARINDEX(@StartString, @MyString) - LEN(@StartString)

SELECT @MySubstring = SUBSTRING(@MyString, @SubStringFrom, @SubStringFor)

SELECT @MySubstring

Then go and do what Aaron says and readup on CHARINDEX and SUBSTRING