CODEWITHSUNDEEP
sqlpsql
user3315620
user3315620

Reputation: 249

In SQL how to check if a string contains a substring in(case insensitive)?

SELECT * FROM products WHERE name LIKE '%a%'

This works fine when case sensitive, but how to search case insensitively?

Thx ahead

Upvotes: 2

Views: 18420

Answers (4)

user359040
user359040

Reputation:

Simple answer:

SELECT * FROM products WHERE lower(name) LIKE '%a%'

Upvotes: 11

shree.pat18
shree.pat18

Reputation: 21757

If you are using TSQL, you might want to specify a case insensitive collation while doing the search.

e.g. 

create table tbl(str varchar(10))

insert into tbl values ('GaP'), ('GAP')

--This will only return 'GaP'
select * from tbl where str collate SQL_Latin1_General_CP1_CS_AS like '%a%'

--This will return both values
select * from tbl where str collate SQL_Latin1_General_CP1_CI_AS like '%a%'

Upvotes: 0

Ferrakkem Bhuiyan
Ferrakkem Bhuiyan

Reputation: 2783

searches for a substring within a larger string you can use CHARINDEX()

Upvotes: 0

Saurabh
Saurabh

Reputation: 73609

  SELECT * FROM products WHERE nam LIKE BINARY '%a%'

collate option can also be used and here is how to make it case sensitive from mysql tutorial.

Upvotes: 0

Related Questions