Looking for sql Answers? Try Ask4KnowledgeBase
Looking for sql Keywords? Try Ask4Keywords

SQL Match open-ended pattern


Example

The % wildcard appended to the beginning or end (or both) of a string will allow 0 or more of any character before the beginning or after the end of the pattern to match.

Using '%' in the middle will allow 0 or more characters between the two parts of the pattern to match.

We are going to use this Employees Table:

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHire_date
1JohnJohnson24681012141140023-03-2005
2SophieAmudsen24791002111140011-01-2010
3RonnySmith24625440262160006-08-2015
4JonSanchez24541246021140023-03-2005
5HildeKnag24680219112180001-01-2000

Following statement matches for all records having FName containing string 'on' from Employees Table.

SELECT * FROM Employees WHERE FName LIKE '%on%';
IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHire_date
3RonnySmith24625440262160006-08-2015
4JonSanchez24541246021140023-03-2005

Following statement matches all records having PhoneNumber starting with string '246' from Employees.

SELECT * FROM Employees WHERE PhoneNumber LIKE '246%';
IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHire_date
1JohnJohnson24681012141140023-03-2005
3RonnySmith24625440262160006-08-2015
5HildeKnag24680219112180001-01-2000

Following statement matches all records having PhoneNumber ending with string '11' from Employees.

SELECT * FROM Employees WHERE PhoneNumber LIKE '%11'
IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHire_date
2SophieAmudsen24791002111140011-01-2010
5HildeKnag24680219112180001-01-2000

All records where Fname 3rd character is 'n' from Employees.

SELECT * FROM Employees WHERE FName LIKE '__n%';

(two underscores are used before 'n' to skip first 2 characters)

IdFNameLNamePhoneNumberManagerIdDepartmentIdSalaryHire_date
3RonnySmith24625440262160006-08-2015
4JonSanchez24541246021140023-03-2005