CODEWITHSUNDEEP
sqlsql-server
Mirt Hlaj
Mirt Hlaj

Reputation: 173

SQL server, Find all columns that are contained in a string

Suppose I have a table:

Id int, 
Name varchar(10)

with values: 

(1, Nick), (2, Mike), (3, Eric)

I want to return all the names that are contained in a string. For example "Nick, Mick" would return Nick and Mike.

I've tried with LIKE but it works the other way around returning the values that contain a string.

I was hoping for somthing like this, but that it actually works.

SELECT * FROM table
WHERE Name.isContained("Nick, Mike");

Result: 

1, Nick and 2, Mike

Upvotes: 0

Views: 75

Answers (4)

Mr. Bhosale
Mr. Bhosale

Reputation: 3096

Check This.

        declare @tmp nvarchar(250)
        SET @tmp = ''
        select  @tmp = @tmp + concat(ID,' , ',name,' and ') 
        from table
        where Name in ('Nick','Mike')

        select SUBSTRING(@tmp, -2, LEN(@tmp))

OutPut :

enter image description here

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4191

Try this:

select name from tbl_sound where  SOUNDEX(`name`) like '%00%'

Upvotes: 1

Serg
Serg

Reputation: 22811

Demo. Finds exact names match

create table demo (
    Id int, 
Name varchar(10));

insert demo
values
(1, 'Nick'), (2, 'Mike'), (3, 'Eric');

declare @prm varchar(200) = 'Nick,Mike,Ann';
select *  
from demo
where ','+ @prm +',' like '%,'+Name+',%'
;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT Name
FROM mytable
WHERE PATINDEX('%'+Name+'%', 'Nick, Mike') > 0

Demo here

Upvotes: 2

Related Questions