String Manipulation (Regualar Expression?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String Manipulation (Regualar Expression?)

Author

Topic

davidagnew37
Starting Member

33 Posts

Posted - 2007-08-20 : 09:07:27

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.

PLEASE NOTE: this is not (or at least I dont think its poss) via a simple substring as 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 Filters,
from TaskLayout

Please provide example.

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 09:44:34

What is your parameter delimiter?

E 12°55'05.25"
N 56°04'39.16"

davidagnew37
Starting Member

33 Posts

Posted - 2007-08-20 : 09:59:11

the Parameter delimeter is always %

Starts with: SavedFilterName=
End With: %

ie. Bold are the values I am pulling out.

SavedFilterName=STAFF_PCOM%2526%26FilterEntryVars_1=EscapePercent=1%2526SavedFilterName=VIP_CP%

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 10:07:09

[code]-- Prepare sample data
DECLARE @var VARCHAR(8000)

SET @var = 'SavedFilterName=STAFF_PCOM%2526%26FilterEntryVars_1=EscapePercent=1%2526SavedFilterName=VIP_CP%'

-- Show the expected output
SELECT STUFF(Data, 1, CHARINDEX('=', Data), '') AS WantedParameters
FROM dbo.fnParseList('%', @var)
WHERE Data LIKE '%SavedFilterName%'[/code]With the help of the function found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-20 : 10:21:55

Without any external function

-- Prepare sample data
DECLARE	@var VARCHAR(8000)

SET	@var = 'SavedFilterName=STAFF_PCOM%2526%26FilterEntryVars_1=EscapePercent=1%2526SavedFilterName=VIP_CP%'

-- Show the expected output
SELECT	STUFF((
		SELECT		DISTINCT TOP 100 PERCENT ',' + SUBSTRING(@var, Start, Finish - Start)
		FROM		(
					SELECT	16 + First AS Start,
						CHARINDEX('%', @var, First + 16) AS Finish
					FROM	(
							SELECT	DISTINCT CHARINDEX('SavedFilterName=', @var, 16 * Number + 1) AS First
							FROM	master..spt_values
							WHERE	Type = 'p'
						) AS d
					WHERE	First > 0
				) AS q
		WHERE		Finish > 0
		ORDER BY	',' + SUBSTRING(@var, Start, Finish - Start)
		FOR		XML PATH('')), 1, 1, '') AS theValues

E 12°55'05.25"
N 56°04'39.16"

davidagnew37
Starting Member

33 Posts

Posted - 2007-08-20 : 10:43:43

this works great!! thank you so much. When I get time tonight I will try and understand whats going on!

Mhackel
Starting Member

12 Posts

Posted - 2007-08-21 : 12:10:57

I have a similar String Manipulation except my data only has spaces as a delimiter, example: date=2007-07-26 time-20:29:57 devname=wesfw2 devid=xxxxxxx, etc. How can I parse through the data from the = to the space and put each piece of data into a particular field order?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-21 : 12:43:04

Exactly as above, but with space and equal-sign as delimiters instead of "%".
Post a new topic if you need more assistance.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources