help related sql 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

SQL Server 2005 Forums

Transact-SQL (2005)

help related sql query

Author

Topic

rammohan
Posting Yak Master

212 Posts

Posted - 2008-10-25 : 07:37:34

Hi,

I have A function

CREATE FUNCTION fn_Split_char(@text VARCHAR(MAX), @delimiter VARCHAR(20) = ' ')
RETURNS

@Strings TABLE ( [position] INT IDENTITY PRIMARY KEY , [value] varchar(100))

AS

BEGIN

DECLARE @index int

SET @index = -1

WHILE (LEN(@text) > 0)

BEGIN -- Find the first delimiter
SET @index = CHARINDEX(@delimiter , @text)

-- No delimiter left?

-- Insert the remaining @text and break the loop
IF (@index = 0) AND (LEN(@text) > 0)

BEGIN
INSERT INTO @Strings

VALUES (CAST(@text AS varchar(100)))
BREAK

END

-- Found a delimiter

-- Insert left of the delimiter and truncate the @text
IF (@index > 1)

BEGIN
INSERT INTO @Strings

VALUES (CAST(LEFT(@text, @index - 1) AS varchar(100)))
SET @text = RIGHT(@text, (LEN(@text) - @index))

END -- Delimiter is 1st position = no @text to insert
ELSE

SET @text = CAST(RIGHT(@text, (LEN(@text) - @index)) AS varchar(100))
END RETURN

END

GO

ANd Here is the code how i am executing it

DECLARE @list VARCHAR(MAX)

SELECT @list ='foo,bar,should not appear'

DECLARE @testTable TABLE

( [testId] INT , [value] VARCHAR(255) )

INSERT @testTable ([testId], [value])

SELECT 1, 'foo'

UNION

SELECT 12412, 'bar'

UNION

SELECT 22222222, 'should not appear'

SELECT tt.*

FROM @testTable tt

JOIN fn_Split_char(@list, ',') filt

ON filt.[value] = tt.[value]

From the above query ia m getting the results which contain value foo,bar and sholud not appear .

I want to ask that How can i make these querie to return result also

when @list contains fo ,ba,not,appear,not appear,

that means like condition also I want That if search wrd is fo

than it can return row containg value foo

and if kywrd is 'not' then it can return row

having value should not appear

and if kywrd is 'not appear' then it can return row

having value should not appear .

I dont want to do that with sql server full text search engine capability

Plz Help Me.

Thanks.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 09:20:24

seems like this is what you want

SELECT tt.*

FROM @testTable tt

JOIN fn_Split_char(@list, ',') filt

ON  tt.[value] LIKE '%' + filt.[value] + '%'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources