SQL Query reverse LIKE [modified]

[Ronni Marker]

Gents, I have a string "abcd1;abcd2;abcd3;abcd4" in my database on an MS SQL there are stored "abcd3" in a column. In the reverse situation I would be able to do a select statement like this Select * from MyTable where MyColumn LIKE '%abcd3%' Is there any easy solution to it in the reverse way so i fx could do something like Select * from MyTable where 'abcd1;abcd2;abcd3;abcd4' LIKE MyColumn Tried this by the way, didnt return any result or error so guess there are some solution to it. Cheers, -- modified at 6:28 Tuesday 14th August, 2007

[Rami Said Abd Alhalim]

this not correct because '%adbcd3%' not equal to '%abcd3%' Select * from MyTable where MyColumn LIKE '%adbcd3%' if you need show resault you must write select statment Select * from MyTable where MyColumn LIKE '%abcd3%'

[Ronni Marker]

Hi Rami, This was an typo in this topic on the example for the normal way to use like statement. Thanks for the notification of the spelling error. /Ronni

[Rami Said Abd Alhalim]

hi ronii you solve the problem if you not solving problem i need more explane about your problem with regards Rami Abd alhalim

[Ronni Marker]

nope didnt solve the problem. please read the whole text. I have string "abcd1;abcd2;abcd3;abcd4" for example posted from a website or an application In my database i have a string "abcd1". I now need to find out if any part of the string ("abcd1;abcd2;abcd3;abcd4") is present in the column who hold fx "abcd1". This have to be done in a single select statement. If done reverse "Find abcd1 in database with abcd1;abcd2;abcd3;abcd4 then a standard Select * from TableName where ColumnName LIKE '%abcd1%'" but is there an fast and easy to do that in a simliar way? just reverse in finding part of a string in the database? /Ronni

[andyharman]

How about:

select * from MyTable
where ';abcd1;abcd2;abcd3;abcd4;' like '%;' + MyColumn + ';%'

Regards Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

[Rami Said Abd Alhalim]

add this function after that you can split string to compare between your string ======================= SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO ALTER FUNCTION [dbo].[fn_Split] (@text varchar(8000), @delimiter varchar(20) = ' ') RETURNS @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) --Name2 Varchar(8000) ) AS BEGIN /*Declare @Strings TABLE ( position int IDENTITY PRIMARY KEY, value varchar(8000) ) */ DECLARE @index int SET @index = -1 Declare @Text2 varchar(8000) SET @Text2=@text Declare @index2 int WHILE (LEN(@text) > 0) BEGIN SET @index = CHARINDEX(@delimiter , @text) IF (@index = 0) AND (LEN(@text) > 0) BEGIN INSERT INTO @Strings VALUES (@text) BREAK END IF (@index >1) BEGIN SET @index2=len(Right(@text, @index - 1)) SET @text2=SUBSTRING(@text,LEN(LEFT(@text, @index+1 )),CHARINDEX(@delimiter , @text)-1 ) INSERT INTO @Strings VALUES (LEFT(@text, @index - 1)) SET @text = RIGHT(@text , (LEN(@text) - @index)) END ELSE SET @text = RIGHT(@text, (LEN(@text) - @index)) END RETURN END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO ======================= after add this function select * from tablename where 'abcd1'in select value from fn_Split('abcd1;abcd2;abcd3;abcd4',';') Rami Abd alhalim

[Ronni Marker]

Great thanks Andy. Worked like a charm. Cheers, Ronni