double split - 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)

double split

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-29 : 09:33:37

Hi, I have a table where I store some category id's like so:
ProductID
ProductName
ProductCategories

And productcatgories is a comma seperated sting like:
5,2,12,18

Now, I'm building a sp which also has a comma seperated string @categories, and it should return all the matching products.

So if I have
Productid ProductName Productcategories
1 Name1 5,2,12
2 Name2 2,1,18
3 Name3 12,18

And I would request @categories = 2,5
it would return products 1 and 2

And if I would request @categories = 18
it would return product 2 and 3

And if I would request @categories = 5,200
it would return product 1

I guess you would have to split the Productcategories and the @categories, and loop through them, but I have no idea how...
Can anyone help/

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-29 : 09:45:52

Go through this link,
if u have doubts,
Let me know..:)

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

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 09:47:31

Hi trouble2

Do you have a productcategories table?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-29 : 09:53:00

Hi trouble2,

check it out.

declare @Productcategories varchar(60)
set @Productcategories='5,2'
declare @x xml
set @x='' + replace(@Productcategories,',','.') + ''
select @x
select * from table_name
where Productcategories in
(
select replace(x.i.value ('.','varchar(50)'),'.','') Productcategories from @x.nodes('//i')x(i)
)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-29 : 09:57:49

Well... right now, I have:

...
(
(@categories = '')
or
([Productcategories] in (Select convert(int,Value) from dbo.Split(@categories,',')))
)

But I don't think that will do the trick...

The secret to creativity is knowing how to hide your sources. (Einstein)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 09:59:41

Here's an old-fashioned approach (assuming you have a productcategories table)...

--sample data
declare @productcatgories table (ProductCategoryId int)
insert @productcatgories
          select 1
union all select 2
union all select 5
union all select 12
union all select 18
union all select 200

declare @products table (ProductID int, ProductName varchar(50), ProductCategories varchar(50))
insert @products
          select 1, 'Name1', '5,2,12'
union all select 2, 'Name2', '2,1,18'
union all select 3, 'Name3', '12,18'

--inputs
declare @myInput varchar(50)
set @myInput = '2,5'

--query
select distinct ProductId, ProductName from (
    select * from @products a inner join @productcatgories b on
        ',' + a.ProductCategories + ',' like '%,' + cast(ProductCategoryId as varchar(10)) + ',%') a
where ',' + @myInput + ',' like '%,' + cast(ProductCategoryId as varchar(10)) + ',%'

/* results
ProductId   ProductName
----------- --------------------------------------------------
1           Name1
2           Name2
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-29 : 10:00:21

And split would be:

function [dbo].[Split]
(@List nvarchar(2000), @SplitOn nvarchar(5))
returns @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
as
begin
while (Charindex(@SplitOn,@List)>0)
begin
insert into @RtnValue (value)
select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
end
insert into @RtnValue (Value)
select Value = ltrim(rtrim(@List))
return
end

The secret to creativity is knowing how to hide your sources. (Einstein)

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-29 : 10:02:39

quote:
Originally posted by VGuyz

Hi trouble2,

check it out.

declare @Productcategories varchar(60)
set @Productcategories='5,2'
declare @x xml
set @x='' + replace(@Productcategories,',','.') + ''
select @x
select * from table_name
where Productcategories in
(
select replace(x.i.value ('.','varchar(50)'),'.','') Productcategories from @x.nodes('//i')x(i)
)

VGuyz - I can't get this working. Any chance you can show it working with the sample data I posted?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-29 : 10:25:31

Doesn't seem to work...
It keeps returning all the rows

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 11:20:00

create a UDF to pass the comma seperated list like this

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

then use it like this

SELECT t1.Productid, t1.ProductName,t1.Productcategories
FROM table t1
CROSS APPLY dbo.ParseValues(t1.Productcategories) b
WHERE ',' + @categories + ',' LIKE '%,' + CAST(b.Val AS varchar(10)) + ',%'

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-29 : 11:32:20

looks ok, should I use
SELECT DISTINCT t1.Productid, t1.ProductName,t1.Productcategories
FROM table t1
CROSS APPLY dbo.ParseValues(t1.Productcategories) b
WHERE ',' + @categories + ',' LIKE '%,' + CAST(b.Val AS varchar(10)) + ',%'

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 11:35:26

quote:
Originally posted by trouble2

looks ok, should I use
SELECT DISTINCT t1.Productid, t1.ProductName,t1.Productcategories
FROM table t1
CROSS APPLY dbo.ParseValues(t1.Productcategories) b
WHERE ',' + @categories + ',' LIKE '%,' + CAST(b.Val AS varchar(10)) + ',%'

The secret to creativity is knowing how to hide your sources. (Einstein)

yup. if you want distinct values. it will repeat for number of items you've in Productcategories field. so better to use distinct

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-30 : 11:13:38

Now, I'm trying to do this twice like so:

[dbo].[Foodservice_Producten_Product] t1
CROSS APPLY [dbo].ParseValues(t1.ProductSoort) b
CROSS APPLY [dbo].ParseValues(t1.productRange) c

But when I try to add this SP, SQL server says:

Msg 102, Level 15, State 1, Procedure FoodService_Product_SearchProducten2, Line 32
Incorrect syntax near '.'.

Which is this line:
CROSS APPLY [dbo].ParseValues(t1.ProductSoort) b

The secret to creativity is knowing how to hide your sources. (Einstein)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 11:26:37

can you post your full query please?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-07-30 : 11:37:32

the strange thing is, I have it working on my local machine, but now I am trying to add it to the production server, and it gives me the error...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[FoodService_Product_SearchProducten2]
@Trefwoord varchar(255),
@Soorten varchar(255),
@Ranges varchar(255)
AS
SELECT DISTINCt
t1.[ProductID]
,t1.[Artikelcode]
,t1.[Productnaam]
,t1.[Gewicht]
,t1.[StuksInDoos]
,t1.[DozenPerPallet]
,t1.[Bereiding]
,t1.[KleineFoto]
,t1.[GroteFoto]
,t1.[KorteOmschrijving]
,t1.[LangeOmschrijving]
,t1.[Trefwoorden]
,t1.[GerelateerdeProducten]
,t1.[ExtraInformatie]
,t1.[Aanduiding]
,t1.[PDF]
,t1.[ProductType]
,t1.[ProductSoort]
,t1.[ProductCategorie]
,t1.[ProductRange]
,t1.[InvoerDatum]
,t1.[LaatstGewijzigd]
FROM
[dbo].[Foodservice_Producten_Product] t1
CROSS APPLY dbo.ParseValues(t1.ProductSoort) b
CROSS APPLY dbo.ParseValues(t1.productRange) c
Where
(
@Soorten = ''
OR
',' + @soorten + ',' like '%,' + cast(b.val as varchar(10)) + ',%'
)
AND
(
@Ranges = ''
OR
',' + @Ranges + ',' like '%,' + cast(c.val as varchar(10)) + ',%'
)
AND
(
@Trefwoord = ''
OR
(
([Productnaam] like '%' + @Trefwoord + '%')
OR
([Artikelcode] like '%' + @Trefwoord + '%')
OR
([KorteOmschrijving] like '%' + @Trefwoord + '%')
OR
([LangeOmschrijving] like '%' + @Trefwoord + '%')
OR
([Trefwoorden] like '%' + @Trefwoord + '%')
OR
([ExtraInformatie] like '%' + @Trefwoord + '%')
)
)

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-01 : 04:39:25

Any ideas what this might cause?
- test server works fine
- production server gives an error when adding the SP

The secret to creativity is knowing how to hide your sources. (Einstein)

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-08-01 : 05:32:29

I get it, I need to put some [] in
CROSS APPLY dbo.ParseValues(t1.ProductSoort) b
CROSS APPLY dbo.ParseValues(t1.productRange) c

But I don't exactly know where... I tried all different sorts of combinations.

Can anyone help, I don't really understand?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources