How split delimiters and get the values

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)

How split delimiters and get the values

Author

Topic

asho
Starting Member

2 Posts

Posted - 2009-10-03 : 05:57:49

Hi,

I am using sql server 2005. A parameters coming to sp like that

aggr | view&true,edit&true | package | view *true,edit&false,delete&true

this parameters are coming from my front end. I need to split this parameters and need to get like that

featurename operation status

========= ======== =====

aggr view true

edit true

and also package like above in array and i get these values every time and insert to table.

Is it possible. Its very urgent. Please suggest me.Hope yours reply

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-03 : 06:29:15

[code]
DECLARE @STR varchar(100)

SELECT @STR ='aggr | view&true,edit&true | package | view &true,edit&false,delete&true'

SELECT feature = LTRIM(feature),
operation = LTRIM(dbo.fnParseString(-1, '&', Data)),
status = LTRIM(dbo.fnParseString(-2, '&', Data))
FROM
(
SELECT feature = dbo.fnParseString(-1, '|', @STR), opr_status = dbo.fnParseString(-2, '|', @STR) UNION ALL
SELECT feature = dbo.fnParseString(-3, '|', @STR), opr_status = dbo.fnParseString(-4, '|', @STR)
) p
CROSS APPLY dbo.fnParseList(',', opr_status)

/*
feature operation status
---------- ---------- ----------
aggr view true
aggr edit true
package view true
package edit false
package delete true

(5 row(s) affected)
*/
[/code]

Using the following 2 functions. You can get it by clicking on the link

fnParseList
fnParseString

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources