Stored procedure with semi colon delimited string

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)

Stored procedure with semi colon delimited string

Author

Topic

ddombadoh
Starting Member

7 Posts

Posted - 2013-06-03 : 10:08:26

Hell All,
I have a table with the following structure;

Column Datatype
id int
title nvarchar(500)
visibility nvarchar(50)
newsDate datetime

The table has sample data below;
id title visibilty newsDate
2 Test title 0; 2013-05-30
3 Test title 2 12;5; 2013-05-30
4 Test title 3 5; 2013-05-30

Now i wrote the stored procedure below;


ALTER PROCEDURE [dbo].[selectUserNewsByRoles] 
	@var varchar(50)
AS
BEGIN
	SELECT * from tbl_news where
	( PATINDEX('%'+left(@var,1)+'%', visibility) > 0
	or PATINDEX('%'+right(@var,1)+'%', visibility) > 0 )
	ORDER BY newsDate
END

When i execute this procedure with '5' as parameter, i get
id title visibilty newsDate
3 Test title 2 12;5; 2013-05-30
4 Test title 3 5; 2013-05-30

which is correct.
However when i pass '1', i get
id title visibilty newsDate
3 Test title 2 12;5; 2013-05-30

which i don't expect, because the visibility does not contain 1, but rather 12.

Any help on this will be appreciated.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-03 : 11:01:35

Hope this helps

( PATINDEX('%'+left(@var,1)+';%', visibility) > 0
or PATINDEX('%'+right(@var,1)+';%', visibility) > 0 )

Note: this depends on data and will work only if every number in the visibility column has a simicolon.

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 00:06:28

You just need this i reckon


ALTER PROCEDURE [dbo].[selectUserNewsByRoles] 
	@var varchar(50)
AS
BEGIN
	SELECT * from tbl_news where
	';' + visibilty + ';' LIKE '%;' + @var + ';%'	
       ORDER BY newsDate
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ddombadoh
Starting Member

7 Posts

Posted - 2013-09-13 : 12:22:48

Thanks visakh16.
Your solution seem to be working until I hit another challenge.
When I pass a string like '5;12', I don't get any result, as I was expecting
id title visibilty newsDate
3 Test title 2 12;5; 2013-05-30
4 Test title 3 5; 2013-05-30

Thanks

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 2013-09-20 : 07:50:11

'Visibility', not 'Visibilty' !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 04:18:26

quote:
Originally posted by ddombadoh

Thanks visakh16.
Your solution seem to be working until I hit another challenge.
When I pass a string like '5;12', I don't get any result, as I was expecting
id title visibilty newsDate
3 Test title 2 12;5; 2013-05-30
4 Test title 3 5; 2013-05-30

Thanks

sorry i had it in wrong order

it should be


ALTER PROCEDURE [dbo].[selectUserNewsByRoles] 
	@var varchar(50)
AS
BEGIN
	SELECT * from tbl_news where
	';' + @var + ';' LIKE  '%;' + visibilty + ';%' 
       ORDER BY newsDate
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources