November 20, 2009 at 2:14 am
Hi everyone,
I have table called products which is having catid field whixh is of type varchar,Where this catid from category table which is of type int,
And i want to select products from products based on the category.
products table looks like this
Pid catid productname
11,2,3,7,8,11,15,16,17,18,19,20,21,Ceramic Mug
21,2,3,4,5,7,8,11,13,16,18,19,20,23, t-shirts
31,2,3,4,5,7,8,11,20,21,24,25,28,calendars
41,2,3,4,5,7,8,10,11,12,15,16,17,18,19,greeting cards
415,16,17,18,19, pen
table
When i below this query for selecting whose catid is 1, it gives products whose product id may be like 11,16 are also selected
select * from products where Catid like '%1%'
but i want to filter based on category id so i am try to convert catid to varchar to int it gives below error
Server: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1,2,3,7,8,11,15,16,17,18,19,20,21,' to data type int.
Some body give me solution for this how i can get only products whose catid is 1 or 2 or so on.
Thanks in advance
November 20, 2009 at 2:33 am
Select * from products where catid = '1'
---------------------------------------------------------------------------------
November 20, 2009 at 2:44 am
Thanks for the reply,
Even i tried with that query it is returning null records,because there is no single value like 1 in catid field, it is a combination of values like 1,2,3 in catid fields
Please give me alternative solution.
Thanks in advance
November 20, 2009 at 2:58 am
ohh sorry, i dint understand completely that catid is a comma seperated value. Does this help?
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5')
Select * from #t
Where charindex('1', t) > 0
---------------------------------------------------------------------------------
November 20, 2009 at 3:04 am
Create this handy function:
ALTER FUNCTION [dbo].[fnSplit](
@InputString varchar(8000)
,@Delimiter varchar(10)
)
RETURNS @TempTable table(
ID int identity(1,1)
,[VALUES] varchar(8000)
)
AS
BEGIN
DECLARE @length int
,@Index int
,@LastIndex int
,@counter numeric
SET @InputString = @InputString + @Delimiter
SET @length = len(@InputString)
SET @Index = 1
SET @counter = 1
WHILE (@counter < @length)
BEGIN
IF charindex(@Delimiter, @InputString, @Index) > 0
BEGIN
SET @lastIndex = charindex(@Delimiter, @InputString, @Index) - @Index
INSERT INTO @TempTable ([Values])
SELECT substring(@InputString, @Index, @lastIndex)
SET @Index = charindex(@Delimiter, @InputString, @Index) + len(@Delimiter)
END
SET @counter = @counter + 1
END
RETURN
END
This is how you can use it
Create table #product(
pid int
,catid varchar(100)
)
INSERT INTO #product
SELECT 1, '1,2,3,7,8,11,15,16,17,18,19,20,21' UNION ALL
SELECT 2, '1,2,3,4,5,7,8,11,13,16,18,19,20,23' UNION ALL
SELECT 3, '1,2,3,4,5,7,8,11,20,21,24,25,28' UNION ALL
SELECT 4, '1,2,3,4,5,7,8,10,11,12,15,16,17,18,19' UNION ALL
SELECT 4, '15,16,17,18,19'
SELECT * FROM #product
WHERE 19 in (select [values] from fnSplit (catid,','))
-Vikas Bindra
November 20, 2009 at 3:08 am
Thank u nabha,
no it will not work ,because
Create table #t(t varchar(10))
INSERT INTO #t VALUES ('1,2,3')
INSERT INTO #t VALUES ('3,4,5')
INSERT INTO #t VALUES ('11,4,5')
Select * from #t
Where charindex('1', t) > 0
this will return 2 recordsets
November 20, 2009 at 3:13 am
MY bad! go with Vikas, that is the right solution for you.
---------------------------------------------------------------------------------
November 20, 2009 at 3:23 am
Thank you Vikas,
When i execute your function i got this error,May i know why this error i am getting
Server: Msg 155, Level 15, State 1, Line 15
'catid' is not a recognized OPTIMIZER LOCK HINTS option.
Thanks
Chandrashekar
November 20, 2009 at 3:28 am
chandrashekar.2512 (11/20/2009)
Thank you Vikas,When i execute your function i got this error,May i know why this error i am getting
Server: Msg 155, Level 15, State 1, Line 15
'catid' is not a recognized OPTIMIZER LOCK HINTS option.
Thanks
Chandrashekar
It works perfectly for me.
Can you post the exact code that you executed?
-Vikas Bindra
November 20, 2009 at 3:34 am
same codes which you sent to me, like creating function, creating table, inserting value into table and selecting from product table
SELECT * FROM products
WHERE catid in (select [values] from dbo.fnSplit (catid,','))
Create table #product(
pid int
,catid varchar(100)
)
INSERT INTO #product
SELECT 1, '1,2,3,7,8,11,15,16,17,18,19,20,21' UNION ALL
SELECT 2, '1,2,3,4,5,7,8,11,13,16,18,19,20,23' UNION ALL
SELECT 3, '1,2,3,4,5,7,8,11,20,21,24,25,28' UNION ALL
SELECT 4, '1,2,3,4,5,7,8,10,11,12,15,16,17,18,19' UNION ALL
SELECT 4, '15,16,17,18,19'
SELECT * FROM #product
WHERE 19 in (select [values] from fnSplit (catid,','))
i think this function looks like solve my problem my problem but i am getting error why i dont know
November 20, 2009 at 3:44 am
Which SQL version are you using?
-Vikas Bindra
November 20, 2009 at 3:48 am
sql server 2000 in my local but in server 2005
November 20, 2009 at 3:56 am
I don't have 2000 box to test.
but I think it won't work in SQL server 2000 as in 2000 you must pass a constant expression to a table valued function used in FROM (that is what i have read long time back but never tried on my own. Here is the link to what i read http://qa.sqlservercentral.com/Forums/Topic305337-8-1.aspx#bm305389 check the reply from "Eddie Wuerch") .
Try it on your 2005 server, it should work.
And ask you company to upgrade to 2005/2008 😉
-Vikas Bindra
November 20, 2009 at 4:35 am
Thanks vikas it works for me .. thank you very much
November 20, 2009 at 10:53 pm
Nabha (11/20/2009)
MY bad! go with Vikas, that is the right solution for you.
Heh... no it's not...that function has a While Loop in it and it's going to be slow. If there's any scalability involved on any of this, not a good idea to use that function.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply