SQL Script Help - SQL Server Forums

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)

SQL Script Help

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-03-12 : 15:52:19

folks:

I need help with this:

Here is the data I have in a table (2 columns - ID and params) The data in params column is in text format. What I want to get from this params column is the "number" before the ">" sign using a SQL Script.

ID params
9 <dosRoot.65>\Test\XYZ.xml <Date.90>
8 /c echois <Out..46> && echo Path <Out..51> && echo Name <Output..50>

the output should be:

ID params
9 90
8 465150

Thanks!

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-12 : 16:43:31

can you clarify please. in there i also see dosroot.65> you want that included?
also is it possible you can change how this data is entered into this table? might be a design issue

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-03-12 : 17:37:59

I want only the 65 and not dosroot. I can't change the design because it is a 3rd party tool. I just want the numbers (65) before ">" so that I can use those numbers to find other data from some other tables.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-12 : 18:00:16

[code]DECLARE @Sample TABLE
(
ID INT,
Params VARCHAR(2000)
)

INSERT @Sample
SELECT 9, '<dosRoot.65>\Test\XYZ.xml <Date.90>' UNION ALL
SELECT 8, '/c echois <Out..46> && echo Path <Out..51> && echo Name <Output..50>'

;WITH Yak (ID, Params, Pos)
AS (
SELECT ID,
Params,
PATINDEX('%.[0-9][0-9]>%', Params)
FROM @Sample

UNION ALL

SELECT ID,
Params,
Pos + 3 + PATINDEX('%.[0-9][0-9]>%', SUBSTRING(Params, Pos + 4, DATALENGTH(Params)))
FROM Yak
WHERE PATINDEX('%.[0-9][0-9]>%', SUBSTRING(Params, Pos + 4, DATALENGTH(Params))) > 0
)

SELECT ID,
SUBSTRING(Params, Pos + 1, 2)
FROM Yak
ORDER BY ID[/code]

E 12°55'05.63"
N 56°04'39.26"

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-03-13 : 09:32:52

Thanks, Peso !

This works great but if the number is more than 2 digits or less than 2 digits it fails. Any idea why? In our case the number of digits can be 1, 2, 3, 4 or maybe 5.

DECLARE	@Sample TABLE
	(
		ID INT,
		Params VARCHAR(2000)
	)

INSERT	@Sample
SELECT	9, '<dosRoot.765>\Test\XYZ.xml <Date.90>' UNION ALL
SELECT	8, '/c echois <Out..46> && echo Path <Out..51> && echo Name <Output..50>'

;WITH Yak (ID, Params, Pos)
AS (
	SELECT	ID,
		Params,
		PATINDEX('%.[0-9][0-9]>%', Params)
	FROM	@Sample

	UNION ALL

	SELECT	ID,
		Params,
		Pos + 3 + PATINDEX('%.[0-9][0-9]>%', SUBSTRING(Params, Pos + 4, DATALENGTH(Params)))
	FROM	Yak
	WHERE	PATINDEX('%.[0-9][0-9]>%', SUBSTRING(Params, Pos + 4, DATALENGTH(Params))) > 0
)

SELECT		ID,
		SUBSTRING(Params, Pos + 1, 2)
FROM		Yak
ORDER BY	ID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-13 : 14:18:33

[code]
;With Your_CTE (ID,ParamData)
AS
(SELECT t.ID,f.Val
FROM Table t
CROSS APPLY dbo.ParseValues(t.params,'>')f
)

SELECT DISTINCT c.ID,
(SELECT RIGHT(ParamData,CHARINDEX('.',REVERSE(ParamData))-1) FROM Your_CTE WHERE ID=c.ID FOR XML PATH(''))
FROM Your_CTE c

[/code]
ParseValues can be found here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115544

sqldba20
Posting Yak Master

183 Posts

Posted - 2009-03-13 : 15:00:19

Thanks, visakh16 !

It gives me this error:

Invalid length parameter passed to the RIGHT function.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-13 : 16:52:25

Try this,

DECLARE	@Sample TABLE
	(
		ID INT,
		Params VARCHAR(2000)
	)

INSERT	@Sample
SELECT	9, '<dosRoot.65>\Test\XYZ.xml <Date.90>' UNION ALL
SELECT	8, '/c echois <Out..46> && echo Path <Out..51> && echo Name <Output..50>'



;With Your_CTE (ID,ParamData)
AS
(SELECT t.ID,f.Val
FROM @Sample t
CROSS APPLY dbo.ParseValues(t.params,'>')f
)


SELECT DISTINCT c.ID,
(SELECT right(ParamData,(CHARINDEX('.',REVERSE(ParamData))-1)) FROM Your_CTE WHERE ID=c.ID and ParamData<>'' FOR XML PATH('')
)Result
FROM Your_CTE c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources