CODEWITHSUNDEEP
sql-serversql-server-2008c#-4.0
M Akela
M Akela

Reputation: 249

sql to check string contains with where clause

in Sql server

I have a following string 

DECLARE @str nvarchar(max);
set @str = "Hello how are you doing today,Its Monday and 5 waiting days";

DECLARE @srch nvarchar(max);
set @srch = " how,doing,monday,waiting";

Now i want to check whether str contains any of string (comma separated string) of srch

I want it in only sql server

is there possibilites to write some query with in clause

like

select from @str where _____  in (select * from CommaSplit(@srch)

where CommaSplit function rerturns rows of @srch comma separted value

I dont want to use cursor or any loop concept as the @srch value can be very long

Thanks

Upvotes: 4

Views: 3692

Answers (3)

Md. Parvez Alam
Md. Parvez Alam

Reputation: 4596

you can use same function to get first string in rows

select string from CommaSplit(@srch,'') where string  in (select * from CommaSplit(@srch)

Upvotes: 2

Sandr
Sandr

Reputation: 776

I don't think that the IN clause is what you need. Instead of this you can use the LIKE construction as following:

 if (select count(*) from CommaSplit(@srch) where @str like '%' + val + '%') > 0
      select 'true'
 else
      select 'false'

In this case you will receive 'true' when at least 1 result of CommaSplit function exists in the @str text. But in this case you also will receive a 'true' value when the result of CommaSplit function is a part of the word in the @str string.

If you need more accurate solution, this can be achieved by the following way: you need to split the @str into the words (also replacing punctuation by spaces beforehand). And, after this, intersect of CommaSplit (@srch) and SpaceSplit(@str) will be the answer on the question. Among this, you also will be able to check which words are matching between two strings.

The overhead of this method is to create function SpaceSplit which is copy of CommaSplit but with another separator. Or the function CommaSplit can be modified to receive a separator as parameter.

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You can use the following common table expressions query to split your string into parts. cte will contain one record per phrase in @srch. In my example below, I show where in @str each of the search phrase is located. It returns 0 if it cannot locate a search phrase.

Note 1: it won't show the location twice if your search phrase is duplicated - you would need another CTE for that.

Note 2: I have to add comma at the end of @srch to make my CTE work. You can do that inside the CTE if you prefer not to change the search string.

DECLARE @str nvarchar(max);
set @str = 'Hello how are you doing today,Its Monday and 5 waiting days';

DECLARE @srch nvarchar(max);
set @srch = 'how,doing,monday,waiting';

set @srch = @srch + ','

-- first split the text into 1 character per row
;with cte
as
(
    select substring(@srch, 1, CHARINDEX(',', @srch, 1) - 1) as Phrase, CHARINDEX(',', @srch, 1) as Idx

    union all

    select substring(@srch, cte.Idx + 1, CHARINDEX(',', @srch, cte.Idx + 1) - cte.Idx - 1) as Phrase, CHARINDEX(',', @srch, cte.Idx + 1) as Idx
    from cte
    where cte.Idx < CHARINDEX(',', @srch, cte.Idx + 1)
)

select charindex(cte.Phrase, @str, 1) from cte

Upvotes: 0

Related Questions