How to pick specific data from a varchar(250) size column

Question

Post reply

How to pick specific data from a varchar(250) size column

Shamshad Ali

SSCarpal Tunnel

Points: 4822
More actions
November 22, 2010 at 3:54 am

#231434

I need a query to pick Bold text from following data (each row has atleast src= ) :
?lid={B50A4527-B797-DD11-89BE-001CC023728E}&src=ASTDMay09&rt=Zs1lzv7TZvVBegouqZhe53YFZKDGjYCD6Wqtcm/YPDycFBoO99Q4kQzZQNC0liFa&stid=1755913&ptu=1
?lid={EC45E52B-EF0E-DE11-9BEA-001CC023728E}&src=FOSE09TY&rt=Zs1lzv7TZvWErc2LeHg63CwYkYKWqYV4QXSMphLHo8yasv+njDPFvku6vDfofSM3&stid=1510061&ptu=1
The criteria is to find src=<only this data to pick from each row of varchar column>till there is & sgin.
suppose the column name is QString from MyTable
Select QString, functionThatReturnCode(QString) as SrcCode from MyTable
Please send me the function code that pick desired values.
Shamshad Ali.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 1

I have done this myself.

Thanks for reading ....

select

left(substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString) ),PATINDEX('%&%',substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString) ))-1) myDesiredValue

from MyTable

Shamshad Ali SSCarpal Tunnel Points: 4822 More actions · Answer 2

Stuck in next step, there are some other values which does not work with my existing function. here is the new sample data:

?link=ProductsAndServices?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1

?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1

Should check both & and _lid both - How should may check both conditions ?

Shamshad Ali.

Brigadur SSCrazy Points: 2097 More actions · Answer 3

Hi,

My first thought would be to use least value of PATINDEX('%&%' ,querystring) resp PATINDEX('%_lid%' ,querystring).

Please let me know if the above sound unclear.

Cheers

tommyh SSCertifiable Points: 6302 More actions · Answer 4

Shamshad Ali (11/22/2010)
Stuck in next step, there are some other values which does not work with my existing function. here is the new sample data:
?link=ProductsAndServices?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1
?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1
Should check both & and _lid both - How should may check both conditions ?
Shamshad Ali.

Well the easy solution (which depends a bit on data) would be to change then '%[&]%' to '%[&_]%'. But that would only work if the string that you do want cant contain a '_'. If it can then something like this might work

select

SubString(myDesiredValue,

1,

case when PATINDEX('%_lid%', myDesiredValue) > 0 then

PATINDEX('%_lid%', myDesiredValue) -1

else Len(myDesiredValue)

end) myDesiredValue

from (select

left(substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString)

),PATINDEX('%&%',substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString)

))-1) myDesiredValue

from MyTable

) x

Simply remove the parts you dont need in the second stage. You could include that in the original select as well... but having stuff readable is good.

/T

WayneS SSC Guru Points: 95460 More actions · Answer 5

Shamshad Ali (11/22/2010)
Stuck in next step, there are some other values which does not work with my existing function. here is the new sample data:
?link=ProductsAndServices?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1
?src=NLVANOV10_lid={5F86099C-AFDD-DF11-80B8-001CC023728E}&stid=1&ptu=1
Should check both & and _lid both - How should may check both conditions ?
Shamshad Ali.

Will there ALWAYS be either the "&" or the "_lid" after the value?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Brigadur SSCrazy Points: 2097 More actions · Answer 6

using REPLACE function to replace '_lid' with '&' in the substring should work too.

select

left(substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString) ),PATINDEX('%&%',replace(substring(QueryString, PATINDEX('%src=%',querystring)+ 4, len(queryString) ),'_lid','&'))-1) myDesiredValue

from MyTable