SQL String Functions
SQL databases provide several SQL Data Types related to text strings such as VARCHAR
, CHAR
, and 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 |
The starting_freeze
column tracks when the apples entered into the refrigerator rooms. This information is provided to customers to meet food regulations.
SQL LIKE Operator
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:
customer_name | apple_variety | delivery_date |
---|---|---|
SMart | Red Delicious | 2020-02-28 |
JB | Red Globus | 2020-03-12 |
SMart | Red Globus | 2020-05-28 |
SMart | Red Delicious | 2020-05-30 |
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 CONCAT
function.
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:
Order Description |
---|
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 |
SQL Functions to Manipulate Text Strings
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 SUBSTRING
, POSITION
and REPLACE
:
SUBSTRING
extracts a part from a string, by specifying the start position and the length to extract.POSITION
returns 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
Result is:
function_call | original | 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
Result is:
function_call | original | 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
Result is:
function_call | original | 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.
Closing Words
In this lesson you learned about several text SQL operators and functions, along with the LIKE
and CONCAT
operators. Examples included text SQL functions like trim()
, lower()
, position()
, replace()
and 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!