Loading

MySQL String Comparison Operator Functions

MySQL String Comparison Operator Functions are used to compare two or more strings.

LIKE Operator:-  Syntax is expression LIKE pattern ESCAPE escape_character i.e.

pat [ESCAPE ‘escape_char’]

It is a logical operator. It checks whether the string is in a specific pattern or not.

MySQL provides two wildcard characters for the LIKE operator constructing pattern

(a) % (percent sign). This wildcard matches any string of zero or more characters.

(b)_(underscore). wildcard matches any single character of the string.

For Example:-

SELECT * FROM employees WHERE firstName LIKE ‘j%’;

This query will list all the details of those employees whose first name will start with ‘j’

SELECT * FROM employees WHERE lastName LIKE ‘%n’;

This query will list all the details of those employees whose last name will end with ‘n’

SELECT * FROM employees WHERE lastName LIKE ‘%an%’;

This query will list all the details of those employees whose last names contain ‘an’

SELECT * FROM employees WHERE firstName LIKE ‘r_n’;

This query will list all the details of those employees whose first names will start with ‘r’ and end with ‘n’

SELECT * FROM employees WHERE firstName LIKE ‘_n%’;

This query will list all the details of those employees whose first name will contain ‘n’ in the second position

SELECT * FROM employees WHERE firstName LIKE ‘n__%’;

This query will list all the details of those employees whose first name will start with n and at least two characters.’

SELECT * FROM products WHERE productCode LIKE ‘%\_01%’;

This query will find products whose product codes contain the string _01

Note:- the default escape character (\).

NOT LIKE Operator :-pat [ESCAPE ‘escape_char’]

This operator finds a string that does not match a specific pattern. For example:-

SELECT * FROM employees WHERE firstName LIKE ‘n__%’;

This query will list all the details of those employees whose first names will not start with n and not at least two characters.

STRCMP(expr1,expr2):- STRCMP() operator function in MySQL is used to compare two strings.

If string1 = string2, this function returns 0

If string1 < string2, this function returns -1

If string1 > string2, this function returns 1

For Example:-

SELECT STRCMP(“PHP exam”, “CSS exam”);

Output:- 1

SELECT STRCMP(“PHP”, “PHP”);

Output:- 0

SELECT STRCMP(“PHP”, “PHP exam”);

Output:- -1

Read also:-

MySQL Arithmetic Operators Example

MySQL Bitwise Operators

MySQL Comparison Operators

MySQL Comparison Functions

MySQL Compound Operators

MySQL Logical Operators

For more detail about operators please check the Manual

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics