0

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

Arian
  • 12,793
  • 66
  • 176
  • 300

3 Answers3

1

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.

Amir Molaei
  • 3,700
  • 1
  • 17
  • 20
1

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);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

use regular expression with NOT LIKE "%[your charecter]%"

Shadiqur
  • 490
  • 1
  • 5
  • 18