Get row that had multi string in name SQL server

Question

I'm using SQL server 2014 I try to get row that have multi-value in name like:

declare @value ='m n'

So the value that return should have 'm' and 'n' in any position I tried to use

Select * from Table where contains(name,N'"*m*" and "*n*"')

But the value return only if it started by 'm' and 'n'

I had also tried:

select * from Table where name like '%m%n%'

the value return only if name contains 'm' then 'n' not 'n' then 'm'.

Note: I'm getting value from parameter so I don't known how many char or what position so I'm using replace on @value to get what I want.

Does this answer your question? [Get rows that contain only certain characters](https://stackoverflow.com/questions/15782767/get-rows-that-contain-only-certain-characters) — Amira Bedhiafi, Dec 10 '19 at 09:08
Unfortunately,No because I'm getting multi char i don't how many so it will not work — Abd ul rahaman Shalata, Dec 10 '19 at 09:15
You should explore full text searching in SQL server that will help you in this matter. — Syed Wahhab, Dec 10 '19 at 10:09
I tried contains as i mentioned in the question but it not work if m and n in middle not start or end — Abd ul rahaman Shalata, Dec 10 '19 at 11:25

LukStorms · Accepted Answer · 2019-12-10T15:04:04.317

Since you want to use that variable.

Then best split the letters or words.

Having the STRING_SPLIT function would be great for this.
But there are more ways to split strings.

The example below does it with a WHILE loop.

CREATE TABLE [Table]
( id INT IDENTITY(101,1) PRIMARY KEY,
  name NVARCHAR(100) NOT NULL
);
GO

✓

INSERT INTO [Table] (name) VALUES
('a munchkin'),
('never ever sever mah lever'),
('saintess'),
('them mammaries');
GO

4 rows affected

DECLARE @value NVARCHAR(1000);
SET @value ='  m  n  ';

DECLARE @values TABLE (
 value NVARCHAR(42)
);
DECLARE @words NVARCHAR(1000);
DECLARE @word NVARCHAR(42);

SET @words = RTRIM(LTRIM(@value))+' ';

WHILE CHARINDEX(' ', @words) > 0
BEGIN
 SET @word = SUBSTRING(@words,0,CHARINDEX(' ',@words))
 SET @words = LTRIM(SUBSTRING(@words,CHARINDEX(' ',@words)+1,LEN(@words)))
 IF @word != '' INSERT INTO @values (value) VALUES (@word);
END;

DECLARE @TotalValues INT;
SET @TotalValues = (select count(distinct value) from @values);

--
-- use the table variable to query the table
--
SELECT * 
FROM [Table] t
WHERE EXISTS
(
   SELECT 1
   FROM @values v
   WHERE t.name LIKE '%'+v.value+'%'
   HAVING COUNT(DISTINCT v.value) = @TotalValues
);

GO

 id | name                      
--: | :-------------------------
101 | a munchkin                
102 | never ever sever mah lever

db<>fiddle here

score 0 · Answer 2 · answered Dec 10 '19 at 09:11

0

You have to separate your conditions like this

select * from Table where name like '%m%' and name like '%n%'

answered Dec 10 '19 at 09:11

jared

473
3
16

I'm getting value from parameter so i don't known how many char or what position so I'm using replace on @value to get what i want – Abd ul rahaman Shalata Dec 10 '19 at 09:14
it will work but i don't know how many values may be one or two or three – Abd ul rahaman Shalata Dec 10 '19 at 10:24

Get row that had multi string in name SQL server

2 Answers2