SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored procedure with semi colon delimited string
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ddombadoh
Starting Member

Ghana
7 Posts

Posted - 06/03/2013 :  10:08:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/03/2013 :  11:01:35  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 06/03/2013 11:01:56
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/04/2013 :  00:06:28  Show Profile  Reply with Quote
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
Go to Top of Page

ddombadoh
Starting Member

Ghana
7 Posts

Posted - 09/13/2013 :  12:22:48  Show Profile  Reply with Quote
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
Go to Top of Page

JaybeeSQL
Posting Yak Master

112 Posts

Posted - 09/20/2013 :  07:50:11  Show Profile  Reply with Quote
'Visibility', not 'Visibilty' !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/22/2013 :  04:18:26  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000