Entry
Database: Language: SQL: Search: How to wild card search in a column? [LIKE / % / _ / [] / [^]]
Mar 13th, 2005 10:40
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 30 December 2003 - 01:24 am -------------------
Database: Language: SQL: Search: How to wild card search in a column?
[LIKE / % / _ / [] / [^]]
---
Overview:
Use the commands:
%
_
[]
[^]
---
---
1. Use of the:
%
sign
---
e.g.
%roi%
finds everything with roi in it
(e.g. du rois, delcroix, ....)
---
---
In general:
---
SELECT * FROM yourtablename WHERE yourcolumnname LIKE '%<your search
string>%'
---
---
e.g.
SELECT * FROM Person WHERE Name LIKE '%John%'
Finds everything in that column with 'John' in it.
---
SELECT * FROM Person WHERE Name LIKE '%John'
Finds everything ending with 'John'
---
SELECT * FROM Person WHERE Name LIKE 'John%'
Finds everything starting with 'John'
---
---
Note: the search is by default case insensitive
(or thus lower case or upper case characters
makes no difference here).
Thus searching for
%JOHN%
or
%john%
or
%John%
...
should give the same results.
---
---
2. Use of the:
_
sign
This matches any single character
e.g.
WHERE Person LIKE '_onny'
gets all rows where the name of that person begins with any letter
followed by the three letters ohn.
For example:
Jonny, Ronny.
---
---
3. Use of the:
[]
signs.
This indicates characters in a certain range, like 0-9, a-z, A-Z, ...
e.g.
WHERE telephonenumber LIKE '[0-9]123%' retrieves all rows where the
telephone number starts with 0123, 1123, 2123, ...
---
---
4. Use of the:
[^]
signs.
This indicates characters not in a certain range.
e.g.
WHERE Person LIKE '[^ABC]%'
gets all rows where the name of person does
not start with A, B or C.
---
---
Internet: see also:
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
