CODEWITHSUNDEEP
sqlsql-serverstringcsv
user979189
user979189

Reputation: 1298

SQL query that returns any of the words found

I need to write a SQL query that returns me the exact words that are found in the string for a given search criteria.

string ex: Hello1 Hi1 Hello2 Hi2 Hey1

search criteria 1: Hello

Result: Hello1, Hello2

search criteria 2: Hi

Result: Hi1, Hi2

search criteria 3: Hey

Result: Hey1

Can anyone please help me with this?

Upvotes: 1

Views: 665

Answers (3)

vvvv4d
vvvv4d

Reputation: 4095

Store the values you want to search in a table and also store your keywords on a table. Search for those keywords in some column and replace the keywords with character of your choice. You can search for them using replace. Then again use replace and remove the character you used to replace the keywords and compare string length to see how much shorter the string gets when you remove the character you used to replace the keywords and then you know how many keywords were found.

Here is a working example and it also supposed multiple keywords per search criteria if you wanted to have more than one keyword.

declare @keywords table ( -- the words we want to find
    Keyword nvarchar(max),
    KeywordGroupID int
)

insert into @keywords select 'Hello',1 union select 'Hi',2 union select 'Hey',3


declare @values table ( -- the strings we want to search
    SearchID int identity(1,1),
    Val nvarchar(max),
    KeywordGroupID int
)

insert into @values select 'Hello1 Hi1 Hello2 Hi2 Hey1',1 union select 'Hello1 Hi1 Hello2 Hi2 Hey1',2 union select 'Hello1 Hi1 Hello2 Hi2 Hey1',3

select
    res.SearchID,
    res.Val,
    res.FoundKeywords,
    res.SearchCriteria

from (
select
    Val,
    sum(cast(len(IdentifiedKeywords)-len(replace(IdentifiedKeywords,'$','')) as int)) FoundKeywords,
    KeywordGroupID,
    keywordslist.list as SearchCriteria,
    SearchID
from (
    select
        v.Val, v.SearchID,k.Keyword,replace(v.Val,k.Keyword,'$') IdentifiedKeywords, v.KeywordGroupID
    from @values v
    cross apply
    (
        select Keyword
        from @keywords K1
        where v.KeywordGroupID =  K1.KeywordGroupID
    ) k
) K2
cross apply
( -- build comma delimited list of the search criteria (key words)
    select
    STUFF(( SELECT ',' + Keyword + ','
                    FROM @keywords K3
                    where K2.KeywordGroupID = K3.KeywordGroupID
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS list
) keywordslist
group by Val,KeywordGroupID, keywordslist.list, SearchID
) res

results

Upvotes: 0

Lumppytaters
Lumppytaters

Reputation: 3

You can use a basic query that uses %'part_of_returned_value'%. The %'---'% works like a "contains".

select u.values from mytable
where u.values like %'hello'%

Upvotes: 0

GMB
GMB

Reputation: 222482

One option splits the phrases to a derived table of words using string_split() (available starting SQL Server 2016), which you then compare against the keyword.

Assuming that the strings are stored in column mystring of table mytable, you would do:

select value
mytable t
cross apply string_split(t.mystring, ' ') 
where value like 'Hello%'

Upvotes: 1

Related Questions