SQL String operations

SQL String operations

SQL String operations : SQL provides LIKE operator for performing pattern matching, this is one of the most important features of SQL, and very handy in retrieving data from the existing database. Instead of specifying the entire string, we can specify only some specific part of the match to be found.
The following two special characters are used extensively for pattern matching:

•Percent (%) :  The % character matches any substring.

•Underscore (_) : The _  character matches any character.

Patterns are case sensitive; that is, upper case characters do not match the lower case characters or vice-versa. Consider the following examples:

•“Sara %” matches any string beginning with “Sara”,

•“ % th % “matches any string containing “ th “ as sub-string, for example, “Saraswathipuram”, “Gangothri”.

•“_” matches any string of exactly three characters.

•“_%” matches any string of at least three characters.

Patterns are expressed in SQL using the like comparison operator. Consider the query ‘ Find all the branch names in all the cities which contain the sub-string “th” in their name”

Select br-name, br-city
From branch
Where br-name like “%th”.

For patterns o include the special pattern characters (that is, %, and _) SQL allows the specification of an escape character.  The escape character is used immediately before a special pattern character to indicate that special pattern character is to be treated like normal character. We define the special character for like comparison using escape keyword.

Consider the following patterns, these use “\” (back slash) as the escape character.

•Like “ab\%cd%” escape  “\” matches all strings beginning with “ab % cd”

•Like “ab\\cd%” escape  “\” matches all strings beginning with “ab \ cd”

Leave a Reply

Your email address will not be published.