SQL queries return columns of data, and these usually contain text fields such as customer names, order numbers and so on. Customer names will typically not have the same format, apart from a maximum permissible length. In this article, the example used will process the strings returned from a SQL query. Two customers names from CUSTNAME have been found by the query:
"Mr. Tiger Woods"
"Mrs. Elin Woods"
The customer title, first name, and surname are all contained in one string.
The blog "SQL Tips and Tricks" invites users to ask a question about SQL, which is then answered for free.
SQL LEFT and RIGHT Commands
These functions return the left and right parts of a string. The syntax for the LEFT function is:
LEFT(CUSTNAME,6)
For the two customers, the leftmost 6 characters are:
"Mr. Ti"
"Mrs. E"
The space between the period and the first name counts as a character.
The RIGHT function has exactly the same syntax:
RIGHT(CUSTNAME,5)
and returns the values
"Woods"
"Woods"
SQL SUBSTR Command
The SUBSTR function is similar to the LEFT and RIGHT functions. It is used to return part of the string. In this example, the 3 characters starting from position 6 are returned:
SUBSTR(CUSTNAME, 6,3) will return
"ige"
"Eli"
It is important not to exceed the length of the entire string. The error will not be fatal, but the result may be NULL or an incomplete result. For instance,
SUBSTR(CUSTNAME, 20,3) will return NULL, since the strings are only 15 characters long, and
SUBSTR(CUSTNAME,14,10) will return "ds", the last 2 characters, even though 10 were requested.
SQL LENGTH Command
To overcome some of the problems found when using the SUBSTR function, it is often useful to use the LENGTH function. This returns the length of the string. It is very easy to use, and the syntax is:
LENGTH(CUSTNAME)
For the two strings "Mr. Tiger Woods" and "Mrs. Elin Woods" the values returned would be:
15
15
SQL CHARINDEX Command - Find Position of Text in a String
This function is used to find the position of a character in a string. In the examples used here, the titles "Mr" and "Mrs" have different lengths, so the LEFT function is not used. To return the title of each customer, it is necessary to find the space or the period, and then return characters to the left of it.
The syntax for the CHARINDEX function to find the period in the customer name, starting at position 1 is:
CHARINDEX(".",CUSTNAME,1)
For the customer names shown, this returns
3
4
The titles can then be found using:
LEFT(CUSTNAME, CHARINDEX(".",CUSTNAME,1))
"Mr."
"Mrs."
SQL CHARINDEX Command - Find Position of Second Instance of Text in a String
To find the forename of each customer, a similar process may be used. The locations of the first space and second space in the string must be found. First, find the position of the first space with:
CHARINDEX(" ", CUSTNAME,1)
Then find the position of the second space in the string:
CHARINDEX(" ", CUSTNAME,1+CHARINDEX(" ", CUSTNAME,1))
i.e. search for the second space starting at the position of the first space plus 1.
The SUBSTR function can then be used to find the first name, which lies between the first and second spaces.
SQL CHARINDEX From Right of String
Sometimes it is useful to find the position of a character as measured from the right, instead of from the left. To do this, the REVERSE command may be used. For example, to find the position of the first instance of the letter "o" from the right, the following will work:
CHARINDEX("o",REVERSE("Mr. Tiger Woods"),1)
This will return the value 3. If the position from the left is needed, the SQL is
LENGTH("Mr. Tiger Woods") - CHARINDEX("o",REVERSE("Mr. Tiger Woods"),1) +1
SQL String Function Summary
After the basic SELECT FROM WHERE SQL commands have been used, it is a short step up to use some more powerful commands such as the string functions LEFT, RIGHT, SUBSTR, and CHARINDEX. These are very useful tools with which to manipulate data found in a query. They are easy to use, and with a little practice they can be used in literally limitless ways. Other ways to refine SQL queries are available at this link, and free online at many training providers.
It is also possible to count the number of occurrences of a character in a string.
SQL String Function References
There are many good courses and references available that describe how to embed SQL into other applications, both online and at most major bookstores. "PHP & mySQL For Dummies" is good for beginners.
The blog "SQL Tips and Tricks" invites users to ask a question about SQL, which is then answered for free.
Martin Bell - Martin holds a B.Sc. degree in chemical engineering, and an M.Sc. degree in electronics and computing. He has spent more than 25 years ...
Join the Conversation