SQL String Functions
SQL databases provide several SQL Data Types related to text strings such as
TEXT. In this lesson you explore some string functions and operators to manipulate string data.
Here is the sample table used in the examples. Building on the apple example, a new table
apple_sells is added to the apple database. The
apple_sells table stores one record per each apple sales transaction.
The schema of the table is:
|Customer Name||Apple Variety||Date of Sell||Tons Sold||Ton Price||Delivery date||Starting Freeze|
|SMart||Red Delicious||2020-02-23||60||54.56||2020-02-28||2020-01-08 10:00|
|JB||Red Globus||2020-03-02||35||61.50||2020-03-12||2020-01-15 09:30|
|JB||Magic Green||2020-04-13||15||58.50||2020-04-22||2020-01-07 08:23|
|SMart||Red Globus||2020-05-21||100||61.00||2020-05-28||2020-01-15 09:30|
|SMart||Red Delicious||2020-05-21||90||54.56||2020-05-30||2020-01-08 10:00|
starting_freeze column tracks when the apples entered into the refrigerator rooms. This information is provided to customers to meet food regulations.
One of the most frequently used string related SQL operators is
LIKE. This operator enables you to create logical expressions: “All the names starting with “Je” or all the cities containing “Lake” in the middle or last names ending with “son”. The
LIKE operator is used in conjunction with two wildcard characters: % (percent) and _ (underscore)
|Wildcards Supported by LIKE Operator|
|% Matches with any string of any length (including the empty string)|
|_ Matches with any character (exactly one character)|
This query returns the sale records of all varieties starting with Red:
SELECT customer_name, apple_variety, delivery_date, FROM apple_sells WHERE apple_variety LIKE ‘Red%’
Here is the result:
A frequently used text operation is to concatenate strings. This operation is SQL dialect specific. For example, some
SQL dialects implement it with double pipe symbol (||), and others use the
This example creates a single column report using the || syntax:
SELECT customer_name || ‘ ordered ‘ || tons_sold || ‘ of ‘ || apple_variety || ‘ to be delivered on ‘ || delivery_date AS order_description FROM apple_sells
The result is:
|SMart ordered 60 of Red Delicious to be delivered on 2020-02-28|
|JB ordered 35 of Red Globus to be delivered on 2020-03-12|
|JB ordered 15 of Magic Green to be delivered on 2020-04-22|
|SMart ordered 100 of Red Globus to be delivered on 2020-05-28|
|SMart ordered 90 of Red Delicious to be delivered on 2020-05-30|
There are several functions to operate with text strings, including:
- Obtain the length of a string
- Convert a string to uppercase or lowercase
- Extract a substring from a string
- Remove blank characters from the beginning or end of a string
Frequent database errors are extra spaces at the end of a string, or having a mix of upper and lower case letters that can cause issues with queries and results. When you are comparing a text column with a text constant, you can convert the string to lowercase and remove any extra space before doing the comparison:
SELECT customer_name, apple_variety, delivery_date, CAST(delivery_date AS TIMESTAMP) - starting_freeze AS freeze_time FROM apple_sells WHERE trim(lower(apple_variety)) = ‘red delicious’
In this example any value like ‘RED DELICIOUS ’ or ‘ Red Delicious’ returns
true in the comparison. An interesting point in the query is the nested invocation of the trim and the lower functions: the result of the lower function call is used as a parameter to the trim function.
Other typical text functions are
SUBSTRINGextracts a part from a string, by specifying the start position and the length to extract.
POSITIONreturns an integer indicating the position of a substring in a container string.
REPLACE()changes one character with another character. You can specify multiple characters to be replaced.
The query in this example creates a report explains these three functions. If you do not understand the query, take a look at the results then examine the query again.
select 'replace(''A white horse'', ''h'', ''H'')' as function_call, 'A white horse' as original, replace('A white horse', 'h', 'H') as result
|replace(‘A white horse’, ‘h’, ‘H’)||A white horse||A wHite Horse|
“replace(‘A white horse’, ‘h’, ‘H’)";“A white horse”;“A wHite Horse”
select 'substring(''A white horse'', 3, 5)' as function_call, 'A white horse' as original, substring('A white horse',3 , 5) as result
|substring(‘A white horse’, 3, 5)||A white horse||white|
“substring(‘A white horse’, 3, 5)";“A white horse”;“white”
select 'position(''horse'' IN ''A white horse'')' as function_call, 'A white horse' as original, position('horse' IN 'A white horse') as result
|position(‘horse’ IN ‘A white horse’)||A white horse||9|
The queries use the different SQL text functions. In the first column of the results you can see the function invocation, in the second column the original string value, and in the third column you can see the result returned by the function.
In this lesson you learned about several text SQL operators and functions, along with the
CONCAT operators. Examples included text SQL functions like
substring(). The next lesson moves on to correlated subqueries, a very powerful kind of subquery that add a lot of new possibilities to the SQL language. Keep going, learn
SQL and increase your skills!
IN THIS PAGE