Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
Read up on SUBSTRING and CHARINDEX. There are plenty of string functions and operators in SQL Server that can help you accomplish these tasks. –  Aaron Bertrand May 17 '12 at 11:39
    
That's a great pointer thanks. I'd accept that as answer. –  dublintech May 17 '12 at 11:47
1  
Also your body says SQL Server 2008 and the tag says SQL Server 2008 R2. In this case it is largely irrelevant but in some cases it might be important to be accurate. –  Aaron Bertrand May 17 '12 at 12:13

2 Answers 2

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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.