Loading

MySQL String Comparison Operator Functions

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

pat [ESCAPE ‘escape_char’]

It is logical operator. It check the string is in specific pattern or not.

MySQL provides two wildcard characters for LIKE operator constructing pattern

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

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

For Example:-

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

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

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

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

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

This query will list all the details of those employee which last name contain ‘an’

SELECT * FROM employees WHERE firstName LIKE ‘r_n’;

This query will list all the details of those employee which first name will start with ‘r’ and end with ‘n’

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

This query will list all the details of those employee which first name will contan ‘n’ at second position

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

This query will list all the details of those employee which first name will start with n and at least two character.’

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 find 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 employee which first name will not start with n and not at least two character.

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

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w