Regular Expressions - String Manipulaltion

[davida37]

SQL Server 2005

I have a text field. (data type text)
The field consists of various Name value pairs.

I need to extract the value of the "SavedFilterName" pair.
i.e.
SavedFilterName=STAFF_PCOM%2526%26FilterEntryVars_1=EscapePercent=1%2526SavedFilterName=VIP_CP%

The value will always have the "SavedFilterName=" to begin with and ends with a "%".

thus, I want to have a result which returns:
STAFF_PCOM,VIP_CP
There could be many of these. I am only looking for the Values of the "SavedFilterName=" pair.

I think I need a regular expression here. Please help.

 

[onpnt]

use substring. regular expressions may be overboard in this case and the overhead of the objects aren't needed for the task
If you are going to use regular expressions then I would not do this in T-SQL and use a CLR function using the .NET System.Text.RegularExpressions class.


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[davida37]

I cant see how substring will acomplish what I need to achieve?

Thanks

 

[onpnt]

ok. it's a basic string manipulation method.
substring(value, [find index of starting position], [find end:index of start calculated to the next index of the char to end it])

you can use charindex to get the positioning


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[onpnt]

maybe adding the need to loop the string until the index = 0 will help understand it also


[sub]____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[davida37]

Hi onpnt, Im not sure you read the question properly. I am not performing a standard substring here. the "SavedFilterName" appears more than once. That is the issue. There could be 1,2,3,4 or more instances of this in the one field. If it only occured once I could do the following.

select
( SUBSTRING(Layout, PATINDEX('%SavedFilterName%', Layout),
PATINDEX('%26FilterEntryType_Count%', Layout) - (PATINDEX('%SavedFilterName%', Layout)) )
as Cible,
from TaskLayout

Please provide examples.

thanks