SQL Server String Operators

SQL Server String Operators

LIKE keyword is available in SQL server to search for a string with the wildcard mechanism.

LIKE keyword is used to specify the condition similar like pattern matching to get the rows from the database table.

LIKE Operator is not case-sensitive.

Wildcard Characters in LIKE Operator

  • % : denotes any string of zero or more characters.
  • _ : denotes single character.
  • [] : denotes any single character specified with in the range.
  • [^] : denotes any single character which is not specified with in the range.

LIKE operator can be used in the WHERE clause.

LIKE Operator Syntax

SELECT column_name1[, column_name2, ....]
FROM table_name
WHERE expression1 LIKE pattern_string

here expression can be constant, variable, return value of function, column value or any combination of constant, variable, function or column value.

pattern_string is the any string constant or string constant with wildcard characters.

Employee Table

Let us consider the example 'Employee' database table.

Employee table output

LIKE Operator with %

To get the rows which country column contains the string either 'US' or 'us'.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE '%US%'
String operators output

To get the rows which country column ends with the string either 'US' or 'us'.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE '%US'
Output:
No rows found.

To get the rows which country column starts with the string either 'US' or 'us'.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE 'US%'
String operators output

LIKE Operator with _

To get the rows which country column contains 3 characters and must starts with the string either 'US' or 'us'.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE 'US_'
String operators output

LIKE Operator with []

To get the rows which country column contains with the string either 'US' or 'us' and followed by either 'A' or 'T' character.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE '%US[AT]%'
String operators output

LIKE Operator with [^]

To get the rows which country column is not starting with the string either 'U' or 'u' and followed by any string.

SELECT Name, DeptId, City, Country, Email, Salary
FROM Employee
WHERE Country LIKE '[^U]%'
String operators output



Python installation

Privacy Policy  |  Copyrightcopyright symbol2020 - All Rights Reserved.  |  Contact us   |  Report website issues in Github   |  Facebook page   |  Google+ page

Email Facebook Google LinkedIn Twitter
^