I have a table that it has a column Name
. The user can specified some characters So that the name column should not contain all those characters. How I can do this dynamically for unknown number of characters?
Thanks
I have a table that it has a column Name
. The user can specified some characters So that the name column should not contain all those characters. How I can do this dynamically for unknown number of characters?
Thanks
Try this:
Select name from tbl
except
select name from tbl
join
(select substring(a.b, v.number + 1, 1) letter
from (select 'WCRTV' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P') chars
on name like '%' + chars.letter + '%'
First, you take a string
as the input, here the input is a static value 'WCRTV'
then using the following code, you can convert the string
into a list of chars
(the credit of this part goes to this link):
select substring(a.b, v.number + 1, 1) letter
from (select 'WCRTV' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'
Then you join the result with your table and finally subtract the result from the table.
If you want to find names that contain the forbidden characters, then use like
:
select name
from tbl
where name like '%[' + @forbidden_characters + ']%'
For example, if the characters to be ignored are numbers, then @forbidden_characters
would contain '0123455789'
.
If the characters were stored in a table instead, you can use JOIN
:
select t.name, f.c
from t join
@forbidden f
on t.name like replace('%[<c>]%', '<c>', f.c);
use regular expression with NOT LIKE "%[your charecter]%"