I want to query something with SQL&#39;s `like` query:

```SQL
SELECT * FROM users  WHERE name LIKE &#39;%m%&#39;
```

How can I achieve the same in MongoDB? I can&#39;t find an operator for `like` in [the documentation][1].

  [1]: http://www.mongodb.org/display/DOCS/Advanced+Queries


How to query MongoDB with &quot;like&quot;

Is there any way to write case-insensitive queries in PostgreSQL, E.g. I want that following 3 queries return same result.

    SELECT id FROM groups where name=&#39;administrator&#39;

    SELECT id FROM groups where name=&#39;ADMINISTRATOR&#39;

    SELECT id FROM groups where name=&#39;Administrator&#39;


How to make &quot;case-insensitive&quot; query in Postgresql?

I have a procedure in SQL that I am trying to turn into Linq:

    SELECT O.Id, O.Name as Organization
    FROM Organizations O
    JOIN OrganizationsHierarchy OH ON O.Id=OH.OrganizationsId
    where OH.Hierarchy like &#39;%/12/%&#39;

The line I am most concerned with is:

    where OH.Hierarchy like &#39;%/12/%&#39;

I have a column that stores the hierarchy like /1/3/12/ for example so I just use %/12/% to search for it.

My question is, what is the Linq or .NET equivalent to using the percent sign?


How to do SQL Like % in Linq?

I looked around some and didn&#39;t find what I was after so here goes.

    SELECT * FROM trees WHERE trees.`title` LIKE  &#39;%elm%&#39;

This works fine, but not if the tree is named Elm or ELM etc...

How do I make SQL case insensitive for this wild-card search?

I&#39;m using MySQL 5 and Apache.

How can I search (case-insensitive) in a column using LIKE wildcard?

Is it possible to combine `LIKE` and `IN` in a SQL Server-Query?

So, that this query

    SELECT * FROM table WHERE column LIKE IN (&#39;Text%&#39;, &#39;Link%&#39;, &#39;Hello%&#39;, &#39;%World%&#39;)

Finds any of these possible matches:

    Text, Textasd, Text hello, Link2, Linkomg, HelloWorld, ThatWorldBusiness

etc...

Combining &quot;LIKE&quot; and &quot;IN&quot; for SQL Server

When using SQL, are there any benefits of using `=` in a `WHERE` clause instead of `LIKE`?

Without any special operators, `LIKE` and `=` are the same, right?

Equals(=) vs. LIKE

I am trying to filter items with a stored procedure using like. The column is a varchar(15). The items I am trying to filter have square brackets in the name.

For example:  `WC[R]S123456`.

If I do a `LIKE &#39;WC[R]S123456&#39;` it will not return anything.

I found some information on using the `ESCAPE` keyword with `LIKE` but I do not understand how to use it to treat the square brackets as a regular string.

How can I escape square brackets in a LIKE clause?

Which one of the following queries is faster (LIKE vs CONTAINS)?

    SELECT * FROM table WHERE Column LIKE &#39;%test%&#39;;

or

    SELECT * FROM table WHERE Contains(Column, &quot;test&quot;);


LIKE vs CONTAINS on SQL Server

The default behaviour of `LIKE` and the other comparison operators, `=` etc is case-sensitive.

Is it possible make them case-insensitive?

Case insensitive searching in Oracle

I have this MySQL query.

I have database fields with this contents

    sports,shopping,pool,pc,games 
    shopping,pool,pc,games 
    sports,pub,swimming, pool, pc, games   

Why does this like query does not work?
I need the fields with either sports or pub or both?

    SELECT * FROM table WHERE interests LIKE (&#39;%sports%&#39;, &#39;%pub%&#39;)


MySQL Like multiple values

&lt;br/&gt;I have the following data in a Table
&lt;br/&gt;PriceOrderShipped
&lt;br/&gt;PriceOrderShippedInbound
&lt;br/&gt;PriceOrderShippedOutbound
&lt;br/&gt;&lt;br/&gt;In SQL I need to write a query which searches for a string in a table. While searching for  a string it should ignore case. For the below mentioned SQL query

    SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME LIKE &#39;%PriceOrder%&#39; 
gives all the above data, whereas

    SELECT DISTINCT COL_NAME FROM myTable WHERE COL_NAME LIKE &#39;%Priceorder%&#39; 
doesn&#39;t give.

Eg. when I search for &#39;PriceOrder&#39; or &#39;priceOrder&#39; it works but &#39;priceorder&#39; or &#39;Priceorder&#39; doesn&#39;t work.
I have tried with the below query using COLLATE, but its not working. Do let me know where im going wrong.

    SELECT DISTINCT COL_NAME FROM myTable WHERE 
    COL_NAME COLLATE latin1_general_cs LIKE &#39;%Priceorder%&#39;



SQL- Ignore case while searching for a string

I just read a post mentioning &quot;full text search&quot; in SQL.  

I was just wondering what the difference between FTS and LIKE are.  I did read a couple of articles but couldn&#39;t find anything that explained it well.

What is Full Text Search vs LIKE

I have been seeing quite a large variation in response times regarding `LIKE` queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results.

I understand that `LIKE` queries are very resource intensive but I just don&#39;t understand why there would be such a large difference in response times. I have built a btree index on the `owner1` field but I don&#39;t think it helps with `LIKE` queries. Anyone have any ideas?

Sample SQL:

    SELECT gid, owner1 FORM parcels
    WHERE owner1 ILIKE &#39;%someones name%&#39; LIMIT 10

I&#39;ve also tried:

    SELECT gid, owner1 FROM parcels
    WHERE lower(owner1) LIKE lower(&#39;%someones name%&#39;) LIMIT 10

And:

    SELECT gid, owner1 FROM parcels
    WHERE lower(owner1) LIKE lower(&#39;someones name%&#39;) LIMIT 10

With similar results.  
Table Row Count: about 95,000.

PostgreSQL LIKE query performance variations

What is the complete list of all special characters for a SQL (I&#39;m interested in SQL Server but other&#39;s would be good too) LIKE clause?

E.g.

    SELECT Name FROM Person WHERE Name LIKE &#39;%Jon%&#39;

**&lt;a href=&quot;http://msdn.microsoft.com/en-us/library/ms179859.aspx&quot;&gt;SQL Server&lt;/a&gt;:**

 1. %
 2. _
 3. [specifier] E.g. [a-z]
 4. [^specifier]
 5. ESCAPE clause E.g. %30!%%&#39; ESCAPE &#39;!&#39; will evaluate 30% as true
 6. &#39; characters need to be escaped with &#39; E.g. they&#39;re becomes they&#39;&#39;re

[**MySQL:**][1]

 1. `%` - Any string of zero or more characters.
 2. `_` - Any single character
 3. ESCAPE clause E.g. %30!%%&#39; ESCAPE &#39;!&#39; will evaluate 30% as true

[**Oracle:**][2]

 1. `%` - Any string of zero or more characters.
 2. `_` - Any single character
 3. ESCAPE clause E.g. %30!%%&#39; ESCAPE &#39;!&#39; will evaluate 30% as true

**Sybase**

 1. %
 2. _
 3. [specifier] E.g. [a-z]
 4. [^specifier]

**[Progress:][4]**

 1. `%` - Any string of zero or more characters.
 2. `_` - Any single character

 [Reference Guide here][3] [PDF]

**[PostgreSQL:][5]**

 1. `%` - Any string of zero or more characters.
 2. `_` - Any single character
 3. ESCAPE clause E.g. %30!%%&#39; ESCAPE &#39;!&#39; will evaluate 30% as true


**[ANSI SQL92:][6]**

 1. %
 2. _
 3. An ESCAPE character *only if specified*.

PostgreSQL also has the `SIMILAR TO` operator which adds the following:

  3. `[specifier]`
  4. `[^specifier]`
  5. `|` - either of two alternatives
  6. `*` - repetition of the previous item zero or more times.
  7. `+` - repetition of the previous item one or more times.
  8. `()` - group items together

The idea is to make this a community Wiki that can become a &quot;One stop shop&quot; for this.


  [1]: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
  [2]: http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like
  [3]: http://www.progress.com/progress/products/documentation/docs/sql92/e92/e92.pdf
  [4]: http://www.progress.com
  [5]: http://www.postgresql.org/docs/7.4/interactive/functions-matching.html
  [6]: http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

List of special characters for SQL LIKE clause

In the [documentation about the LIKE operator][1], nothing is told about the case-sensitivity of it. Is it? How to enable/disable it?

I am querying `varchar(n)` columns, on an Microsoft SQL Server 2005 installation, if that matters.

  [1]: http://msdn.microsoft.com/en-us/library/ms179859.aspx


Is the LIKE operator case-sensitive with SQL Server?

I can use the SQL `Like` Operator using `pymongo`,

    db.test.find({&#39;c&#39;:{&#39;$regex&#39;:&#39;ttt&#39;}})

But how can I use `Not Like` Operator?

I tried

    db.test.find({&#39;c&#39;:{&#39;$not&#39;:{&#39;$regex&#39;:&#39;ttt&#39;}})

but got error:

&gt; OperationFailure: $not cannot have a regex

How can I use &#39;Not Like&#39; operator in MongoDB

in MySQL 

    select * from record where register_date like &#39;2009-10-10%&#39;

What is the syntax in SQL Server?

SQL Server datetime LIKE select?

Is there any way to compare strings in a C# LINQ expression similar to SQL&#39;s `LIKE` operator?

Suppose I have a string list. On this list I want to search a string. In SQL, I could write:

    SELECT * FROM DischargePort WHERE PortName LIKE &#39;%BALTIMORE%&#39;

Instead of the above,  query want a linq syntax.
 
    using System.Text.RegularExpressions;
    …

    var regex = new Regex(sDischargePort, RegexOptions.IgnoreCase);
    var sPortCode = Database.DischargePorts
                    .Where(p =&gt; regex.IsMatch(p.PortName))
                    .Single().PortCode;

My above LINQ syntax does not work. What have I got wrong?


LIKE operator in LINQ

I am trying to write a JPQL query with a like clause:

    LIKE &#39;%:code%&#39;

I would like to have code=4 and find

&lt;pre&gt;
455
554
646
...
&lt;/pre&gt;

I cannot pass `:code = &#39;%value%&#39;`

    namedQuery.setParameter(&quot;%&quot; + this.value + &quot;%&quot;);

because in another place I need `:value` not wrapped by the `%` chars. Any help?

Parameter in like clause JPQL

&gt; **Possible Duplicate:**  
&gt; [Mysql Like Case Sensitive](https://stackoverflow.com/questions/8083455/mysql-like-case-sensitive)  

&lt;!-- End of automatically inserted text --&gt;

Mysql ignores case for its LIKE comparisons.

How can you force it to perform case-sensitive LIKE comparisons?

How do you force mysql LIKE to be case sensitive?

I want to use Eloquent&#39;s active record building to build a search query, but it is going to be a LIKE search. I have found the `User::find($term)` or `User::find(1)`, but this is not generating a like statement. I&#39;m not looking for a direct answer, but if someone could at least give me a direction to look in that&#39;d be great!




Using Eloquent ORM in Laravel to perform search of database using LIKE

I have the following code:
            
    const string Sql = 
        @&quot;select distinct [name] 
          from tblCustomers 
          left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
          where (tblCustomer.Name LIKE &#39;%@SEARCH%&#39; OR tblCustomerInfo.Info LIKE &#39;%@SEARCH%&#39;);&quot;;
                        
    using (var command = new SqlCommand(Sql, Connection))
    {       
        command.Parameters.AddWithValue(&quot;@SEARCH&quot;, searchString);
        ...
    }

This does not work, I tried this as well:

    const string Sql = 
        @&quot;select distinct [name] 
         from tblCustomers 
         left outer join tblCustomerInfo on tblCustomers.Id = tblCustomerInfo.CustomerId  
         where (tblCustomer.Name LIKE @SEARCH OR tblCustomerInfo.Info LIKE @SEARCH );&quot;;
                              
    using (var command = new SqlCommand(Sql, Connection))
    {       
        command.Parameters.AddWithValue(&quot;@SEARCH&quot;, &quot;&#39;%&quot; + searchString + &quot;%&#39;&quot;);
        ...
    }

but this does not work as well. What is going wrong? Any suggestions?


Use of SqlParameter in SQL LIKE clause not working

How do I escape a string in SQL Server&#39;s stored procedure so that it is safe to use in `LIKE` expression.

Suppose I have an `NVARCHAR` variable like so:

    declare @myString NVARCHAR(100);

And I want to use it in a `LIKE` expression:

    ... WHERE ... LIKE &#39;%&#39; + @myString + &#39;%&#39;;

How do I escape the string (more specifically, characters that are meaningful to `LIKE` pattern matching, e.g. `%` or `?`) in T-SQL, so that it is safe to use in this manner?

For example, given:

    @myString = &#39;aa%bb&#39;

I want:

    WHERE ... LIKE &#39;%&#39; + @somehowEscapedMyString + &#39;%&#39;

to match `&#39;aa%bb&#39;`, `&#39;caa%bbc&#39;` but not `&#39;aaxbb&#39;` or `&#39;caaxbb&#39;`.

Escape a string in SQL Server so that it is safe to use in LIKE expression

I stumbled into a delicate SQL problem when I needed to use a value from a field inside a LIKE %..% statement.

Example:

    SELECT t1.Notes, t2.Name
    FROM Table1 t1, Table2 t2
    WHERE t1.Notes LIKE &#39;%t2.Name%&#39;

This is only an example from the top of my head to show what I need to do (I know this will not work). I need to use the value of t2.Name inside the LIKE %..%
  
I guess this is trivial when you know it ;)

Use LIKE %..% with field values in MySQL

The field `table`.`name` contains &#39;Stylus Photo 2100&#39; and with the following query

    SELECT `name` FROM `table` WHERE `name` LIKE &#39;%Stylus 2100%&#39;

I get no results. Of course i would if i searched

    SELECT `name` FROM `table` WHERE `name` LIKE &#39;%Photo 2100%&#39;

How can I select the record by searching &#39;Stylus 2100&#39; ?

Thanks 

MySQL SELECT LIKE or REGEXP to match multiple words in one record

For whatever reason I have these classes called `.main_sub1`, `.main_sub2` etc. Never mind why I can&#39;t have `.main .sub`.

Is there a way with jQuery, sort of in the way it is possible to do with attributes, to get the classes containing `main`?

Element or class LIKE selector for jQuery?

I&#39;ve found a solution for finding the position of an underscore with PATINDEX :

    DECLARE @a VARCHAR(10)  
    SET     @a = &#39;37_21&#39;
    
    PRINT PATINDEX(&#39;%_%&#39;, @a)                    -- return 1 (false)
    PRINT PATINDEX(&#39;%!%&#39;, REPLACE(@a, &#39;_&#39;, &#39;!&#39;)) -- return 3 (correct)

Have you other ideas? Like a way to escape the underscore character?  


How to escape underscore character in PATINDEX pattern argument?

I have a MySQL table for which I do very frequent `SELECT x, y, z FROM table WHERE x LIKE &#39;%text%&#39; OR y LIKE &#39;%text%&#39; OR z LIKE &#39;%text%&#39;` queries. Would any kind of index help speed things up?

There are a few million records in the table. If there is anything that would speed up the search, would it seriously impact disk usage by the database files and the speed of `INSERT` and `DELETE` statements? (no `UPDATE` is ever performed)

&lt;b&gt;Update&lt;/b&gt;: Quickly after posting, I have seen a lot of information and discussion about the way `LIKE` is used in the query; I would like to point out that the solution &lt;b&gt;must&lt;/b&gt; use `LIKE &#39;%text%&#39;` (that is, the text I am looking for is prepended and appended with a % wildcard). The database also has to be local, for many reasons, including security.

How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

I have mysql table that has a column that stores xml as a string. I need to find all tuples where the xml column contains a given string of 6 characters. Nothing else matters--all I need to know is if this 6 character string is there or not. 

So it probably doesn&#39;t matter that the text is formatted as xml.

Question: how can I search within mysql? 
ie 
`SELECT * FROM items WHERE items.xml [contains the text &#39;123456&#39;]`

Is there a way I can use the LIKE operator to do this?

Search for string within text column in MySQL

SQL query for a carriage return in a string and ultimately removing carriage return

I have some data in a table and there are some carriage returns in places where I don&#39;t want them. I am trying to write a query to get all of the strings that contain carriage returns.

I tried this

    select * from Parameters
    where Name LIKE &#39;%&quot;\n&quot; %&#39;

Also

    select * from Parameters
    where Name LIKE &#39;\r&#39;

&#39;

Both are valid SQL but are not returning what I am looking for. Do I need to use the *Like* command or a different command? How do I get the carriage return into the query?

The carriage return is not necessarily at the end of the line either (may be in the middle).

SQL query for a carriage return in a string and ultimately removing carriage return

I have these text in my db,

    categories_posts
    categories_news
    posts_add
    news_add

And I don&#39;t want to select the rows with `categories`, I use a query something like this,

    SELECT *
    	FROM developer_configurations_cms
    		
    	WHERE developer_configurations_cms.cat_id = &#39;1&#39;
    	AND developer_configurations_cms.cfg_variables LIKE &#39;%parent_id=2%&#39;
    	AND developer_configurations_cms.cfg_name_unique NOT LIKE &#39;%categories%&#39;

but it returns these two in the output as well...

    categories_posts
    categories_news

How can I ignore them in my query?

Thanks.

MySQL: NOT LIKE

I can&#39;t figure out what the underscore character does in an SQLite `like` statement.
The wildcard character, `%`, is probably the same as in most other SQL databases.

So, what does the `_` character do?

SQLite Like % and _

I am trying to execute the sql query:

    select * from table where column like &#39;%value%&#39;;

But the data is saved as &#39;Value&#39; ( V is capital ).

When I execute this query i don&#39;t get any rows. 
How do i make the call such that, it looks for &#39;value&#39; irrespective of the casing of the characters ?

SQL SELECT LIKE (Insensitive casing)

I have 2 tables, say table A and table B and I want to perform a join, but the matching condition has to be where a column from A &#39;is like&#39; a column from B meaning that anything can come before or after the column in B:

for example: if the column in A is &#39;foo&#39;.  Then the join would match if column in B is either: &#39;fooblah&#39;, &#39;somethingfooblah&#39;, or just &#39;foo&#39;.  I know how to use the wildcards in a standard like statement, but am confused when doing a join.  Does this make sense? Thanks.

how to use a like with a join in sql?

This is my code but it dosn&#39;t work:

    $param = &quot;%{$_POST[&#39;user&#39;]}%&quot;;
    $stmt = $db-&gt;prepare(&quot;SELECT id,Username FROM users WHERE Username LIKE ?&quot;);
    $stmt-&gt;bind_param(&quot;s&quot;, $param);
    $stmt-&gt;execute();
    $stmt-&gt;bind_result($id,$username);
    $stmt-&gt;fetch();


This code it doesn&#39;t seem to work. I have searched it a lot.
Also it may return more than 1 row.
So how can I get all the results even if it returns more than 1 row?


How can I with mysqli make a query with LIKE and get all results?

I have a table with 200 records out of which 10 records has text containing the word &#39;TAX&#39;.

When I&#39;m executing

    Select * from tbl1 WHERE [TextCol] LIKE &#39;%TAX%&#39;
 then I get the result set with those 10 records correctly .

But when I am trying to exclude those records by

    Select * from tbl1 WHERE [TextCol] NOT LIKE &#39;%TAX%&#39;
 it&#39;s returning 100 records only, instead of 190.

NOT LIKE and LIKE not returning opposite result

I have been trying to get this working for quite a while now but it just doesn&#39;t seem to work, maybe it is is not even possible, what i am wanting to do is to perform a MySQL join query using like, such as this example i found...

    SELECT *
    FROM Table1
    INNER JOIN Table2 ON Table1.col LIKE &#39;%&#39; + Table2.col + &#39;%&#39;

but it just doesn&#39;t seem to work at all, any help that can be given would be brilliant, thanks !

MySQL join query using like?

I have a select query where I am trying to search strings for multiple patterns

    LIKE (&#39;%this%&#39; or &#39;%that%&#39; ) and something=else
Returns zero results

However 

    LIKE &#39;%this%&#39; and something=else
returns results
and 

    LIKE &#39;%that%&#39; and something=else
returns result

Is it possible to get all my results into one query?  If a string matches both, how will it handle that?

All Sql Like Solutions on Gang of Coders

Total of 38 Sql Like Solutions

How to query MongoDB with "like"

How to make "case-insensitive" query in Postgresql?