Stack Overflow
Stack Overflow

Questions tagged [sql-like]

The LIKE predicate is used to search for a specific pattern in a column.

The LIKE predicate is used to search for a specified pattern in a column. The wildcard character % stands for a sequence of zero or more characters, the wildcard character _ stands for a single character.

Examples of usage:

Starts with P

SELECT * FROM Person
WHERE name LIKE 'P%'

Ends with P

WHERE name LIKE '%P'

Contains P (will match anywhere in the string so this potentially returns a lot of rows)

WHERE name LIKE '%P%'

Has an E as second character:

WHERE name LIKE '_E%'

You can also combine any other conditions using AND or OR operators.

References

3176 questions
15
votes
3 answers

LIKE and NULL in WHERE clause in SQL

I have a store procedure which i have planned to use for search and get all values. Scenario: If the parameter passed is NULL it should return all the values of the table and if the parameter passed is not NULL it should return the values according…
A Coder
  • 3,039
  • 7
  • 58
  • 129
15
votes
5 answers

Like operator for integer

I have a column of type bigint (ProductSerial) in my table. I need to filter the table by the Product serial using like operator. But I found that, like operator can't be used for integer type. Is there any other method for this (I don't want to use…
Raging Bull
  • 18,593
  • 13
  • 50
  • 55
14
votes
1 answer

MySQL - Why are COLLATION rules ignored by LIKE operator for German ß character

I'm running the following select statements on MySQL 5.0.88 with utf8 charset and utf8_unicode_ci collation: SELECT * FROM table WHERE surname = 'abcß'; +----+-------------------+------+ | id | forename | surname …
sub
  • 157
  • 1
  • 5
14
votes
5 answers

MySQL LIKE + php sprintf

$test = sprintf("SELECT * FROM `table` WHERE `text` LIKE '%%s%'", mysql_real_escape_string('test')); echo $test; output: SELECT * FROM `table` WHERE `text` LIKE '%s but it should output: SELECT * FROM `table` WHERE `text` LIKE '%test%'
user317005
14
votes
5 answers

JPQL: cast Long to String to perform LIKE search

I have the following JPQL query: SELECT il FROM InsiderList il WHERE ( il.deleteFlag IS NULL OR il.deleteFlag = '0' ) AND il.clientId = :clientId AND ( LOWER( il.name ) LIKE :searchTerm OR il.nbr LIKE :searchTerm OR LOWER(…
Kawu
  • 13,647
  • 34
  • 123
  • 195
14
votes
2 answers

SQL Server, combining % and [] wildcards in LIKE statement

In t-sql, is there a way to do pattern matching in a like statement such that you can search for 1 or more characters in a given set? To be specific, I'm trying to come up with a LIKE statement that will return strings that begin with 1 or more…
Jim
  • 224
  • 1
  • 3
  • 10
14
votes
1 answer

SQLite accent-insensitive search

Is there any way to do an accent-insensitive LIKE query in SQLite? For example, this query: SELECT * FROM users WHERE name LIKE "Andre%" would return: André the Giant Andre Agassi etc. I'm using Qt with QSqlDatabase if it makes any difference.
laurent
  • 88,262
  • 77
  • 290
  • 428
14
votes
10 answers

Emulating SQL LIKE in JavaScript

How can I emulate the SQL keyword LIKE in JavaScript? For those of you who don't know what LIKE is, it's a very simple regex which only supports the wildcards %, which matches 0 or more characters, and _ which matches exactly one character. However,…
erikkallen
  • 33,800
  • 13
  • 85
  • 120
14
votes
5 answers

SQL 'LIKE' operator in Hibernate Criteria API

I want to implement some universal filter with Hibernate Criteria. It should work like LIKE operator from SQL: SELECT * FROM table WHERE table.ANYCOLOUMNHERE LIKE '%'||anyvaluehere||'%' I have Map where key is a column name, and…
Divers
  • 9,531
  • 7
  • 45
  • 88
14
votes
1 answer

How to concatenate % and column name in a LIKE statement

I'm trying this: SELECT FacilityID, FacilityName, CMSProviderID, [Provider Number] FROM G2_Facility, SCIPHospitalCompare WHERE [Provider Number] LIKE '%' + CMSProviderID + '%'; And I get: Msg 245, Level 16, State 1, Line 1 Conversion failed when…
Tommy B.
  • 3,591
  • 14
  • 61
  • 105
13
votes
7 answers

How to search millions of record in SQL table faster?

I have SQL table with millions of domain name. But now when I search for let's say SELECT * FROM tblDomainResults WHERE domainName LIKE '%lifeis%' It takes more than 10 minutes to get the results. I tried indexing but that didn't help. What…
user737063
  • 139
  • 1
  • 1
  • 3
13
votes
3 answers

difference between like and regex operator

I'm learning MySQL now. I need your help in understanding the difference between these queries: select id from tab where id like '000'; select id from tab where id regex '000';
user494841
  • 133
  • 1
  • 1
  • 5
13
votes
1 answer

PostgreSQL performance difference between LIKE and regex

Could someone explain such a big performance difference between these SQLs ? SELECT count(*) as cnt FROM table WHERE name ~ '\*{3}'; -- Total runtime 12.000 - 18.000 ms SELECT count(*) as cnt FROM table WHERE name ~ '\*\*\*'; -- Total runtime 12.000…
dmikam
  • 992
  • 1
  • 18
  • 27
13
votes
2 answers

How to use a percent (%) in a LIKE without it being treated as a wildcard?

I have a database that is supposed to contain all top-level and second-level domain names. But the feed I'm parsing contains a lot of sub folders, I'd like to delete any row that contains any % sign in it, but I am having a hard time figuring out…
Robert82
  • 370
  • 2
  • 4
  • 15
12
votes
5 answers

Like operator in LINQ to Objects

I'm trying to emulate the LIKE operator in LINQ to Objects. Here my code: List list = new List(); list.Add("line one"); list.Add("line two"); list.Add("line three"); list.Add("line four"); list.Add("line five"); list.Add("line…
Anton Semenov
  • 6,227
  • 5
  • 41
  • 69
1 2 3
…
99 100