SQL Server Using Wildcard Within In

use % wildcard with ' in ' operator in sql server

Replace IN with a JOIN ON

  select distinct t.* from tblProducts t 
     inner join CSVToTable(@At2) f  
        on t.category1 like f.field1

Can the IN operator use LIKE-wildcards (%) in Oracle?

Select * from myTable m
where m.status not like 'Done%' 
and m.status not like 'Finished except%'
and m.status not like 'In Progress%'

SQL In() with Wildcard Operators For MULTIPLE COLUMNS

You could just concatinate them with a separator.

SELECT * 
FROM your_table 
WHERE CONCAT_WS('|', col1, col2, col3, col4) LIKE '%text%'

In MySql 8 one could also use an EXISTS for this

SELECT * 
FROM your_table t
WHERE EXISTS (
  select 1
  from (
    select t.col1 as col union all
    select t.col2 union all
    select t.col3 union all
    select t.col4
  ) q
  where col LIKE '%text%'
);

Demo on db<>fiddle here

SQL Server IN statement with wildcard

How about this:

SELECT * FROM contacts
WHERE Left(zipcode,5) IN ('45211','45213')

Can storing wildcard strings in a column (to be used with LIKE operator) cause unexpected query results or security issues?

The security flaw would -- conceivably -- arise if user input is put directly into the table without validation and users are limited to what they can see.

That is, if '%' could allow someone to see data they shouldn't.

However, using a column name for the like pattern is not a SQL injection risk, in the sense that it cannot cause another command to "inadvertently" run. And if you are putting the patterns into the table for matching purposes, there is no additional risk.

There might be a concern with performance, but that is another issue entirely.

Using Wildcard For Range of Characters In T-SQL

We don't know the version, but if you're only 2016+ TRANSLATE would likely work well here:

DECLARE @ReplaceChars varchar(50) = '.''`(){}[]!"£$%^&*-=_+';

SELECT REPLACE(REPLACE(TRANSLATE(YourColumn, @ReplaceChars, REPLICATE(LEFT(@ReplaceChars, 1), LEN(@ReplaceChars)),LEFT(@ReplaceChars,1),''),'  ',' ')
FROM ...

You'll still need to use REPLACE on the left most character, and the double spaces though.

Why the % wildcard is not working in SQL Server as contents of a variable?

By declaring as a variable the wildcard becomes just another literal. Also, a length should be assigned to the VARCHAR. Then this works

DECLARE @myvar varchar(10) = 'blah'
SELECT * FROM TABLE WHERE column like @myvar+'%'

Please see this WORKING example

drop table if exists #Stock;
go
create table #Stock(
  some_col      varchar(10) not null);

insert #Stock(some_col) values ('Stock abc'),('Stock abc'),('Stock abc');

DECLARE @myvar varchar = 'Stock%';
SELECT * FROM #Stock WHERE some_col like @myvar;

DECLARE @myvar2 varchar = 'Stock';
SELECT * FROM #Stock WHERE some_col like @myvar2+'%';

---

Related Topics