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:

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)

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 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:

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:

“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:

“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:

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!