.NET 2.0 SQL Split-Type Query - 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

General SQL Server Forums

New to SQL Server Programming

.NET 2.0 SQL Split-Type Query

Author

Topic

levibeckman
Starting Member

1 Post

Posted - 2008-12-02 : 18:34:27

So let's say I have the following:

Sample Category(varchar(256)) Field Value = 'test, bar, foo'

(@strCategory = 'foo')
Returns all records with 'foo' in Category.

-------------------------------------------------------

SELECT ID, Metasort, PostDate, VideoTitle, LinkTitle, ShortDescription, Description, Thumbnail, Address, Category, SSMA_TimeStamp
FROM Videos
WHERE (upper(Category) LIKE upper(@strCategory)) OR
(upper(Category) LIKE upper(@strCategory) + ',%') OR
(upper(Category) LIKE upper(@strCategory) + ' %') OR
(upper(Category) LIKE '%,' + upper(@strCategory) + ',%') OR
(upper(Category) LIKE '% ' + upper(@strCategory) + ' %') OR
(upper(Category) LIKE '%, ' + upper(@strCategory) + ',%') OR
(upper(Category) LIKE '% ' + upper(@strCategory) + ' %') OR
(upper(Category) LIKE '%,' + upper(@strCategory)) OR
(upper(Category) LIKE '% ' + upper(@strCategory)) OR
(upper(Category) LIKE '%, ' + upper(@strCategory)) OR
(upper(Category) LIKE '% ' + upper(@strCategory))

-------------------------------------------------------

HOWEVER, I want to have something like: (forgive my total utter ignorance)

(@strCategory = 'foo,bar')
Want to return all unique records containing 'foo' AND/OR 'bar' in Category, ORDER BY order of delimited strings, then Metasort field...

-------------------------------------------------------

DECLARE @arrSplitString = SPLIT(',', @strCategory)

SELECT ID, Metasort, PostDate, VideoTitle, LinkTitle, ShortDescription, Description, Thumbnail, Address, Category, SSMA_TimeStamp
FROM Videos
WHERE
FOR EACH TRIM(@strItem) IN @arrSplitString
(
(upper(Category) LIKE upper(@strItem)) OR
(upper(Category) LIKE upper(@strItem) + ',%') OR
(upper(Category) LIKE upper(@strItem) + ' %') OR
(upper(Category) LIKE '%,' + upper(@strItem) + ',%') OR
(upper(Category) LIKE '% ' + upper(@strItem) + ' %') OR
(upper(Category) LIKE '%, ' + upper(@strItem) + ',%') OR
(upper(Category) LIKE '% ' + upper(@strItem) + ' %') OR
(upper(Category) LIKE '%,' + upper(@strItem)) OR
(upper(Category) LIKE '% ' + upper(@strItem)) OR
(upper(Category) LIKE '%, ' + upper(@strItem)) OR
(upper(Category) LIKE '% ' + upper(@strItem))
)
IF NOT @arrSplitString.EOF THEN
OR
END IF
LOOP
ORDER BY ORDEROF(@strItem), Metasort(ASCENDING)

-------------------------------------------------------

Now obviously I have no idea what I am doing, and the syntax is nowhere near what it should be, but hopefully you get the idea.

I want to pass a query one variable, and have the the query split that variable and return a record set containing any of the categories passed.

Anyone wanna take a swing at it? I'll be your best friend?

~Code Monkey

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-09 : 08:16:14

Create this function, then use it in procedure to split the variable:
eg. Select split(@Var,',')

CREATE FUNCTION [dbo].[Split] ( @ItemList NVARCHAR(4000), @delimiter CHAR(1) )
RETURNS @IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item) IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 09:37:35

why are you storing values like this? have you heard about normalisation?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources