5.1 Text Strings

• Concatenate or combine strings

SQL use the pipe or vertical bar key (||) to concatenate strings. The following example concatenates the company name and the contact name. What it does are:

1. use SELECT statement to pull the company name and contact name
2. concatenate these two together

SELECT
CompanyName, ContactName, CompanyName || '  ( ' || ContactName || ' ) '
FROM customers

It’s important to know that the different relational database management systems use different formats. SQL server, for example, uses the plus sign (+) instead of a pipe (||).

• Trim strings

With trimming function, you can either trim everything off the front and the back, or you can just trim it from the right or left. To do this, we’re going to use the simple function called TRIM. We also have RTRIM and LTRIM, for right trim and left trim respectively.

In the following example, there are trailing spaces before and after the string. The TRIM takes care of all of the trailing spaces which is an easy way to clean up your data and will save you a lot of hassle in the long run.

SELECT TRIM ("  You the best,      ")  AS TrimmedString;

• Use the substring function

Substring returns the specified number of characters from a particular position of a given string

First_name	substr(first_name, 2, 3)
Alexander	lex
Bruce	ruc
David	avi
Valli	all
Diana	ian

In substr(first_name, 2, 3), the first one is string name, the second one is string position, and the third is number of characters to be returned.

SELECT first_name, SUBSTR(first_name, 2, 3)
FROM employees
WHERE department_id = 60;

• Change the case of strings

This is an easy one : )

SELECT UPPER(column_name) FROM table_name;
SELECT LOWER(column_name) FROM table_name;
SLECT UCASE(column_name) FROM table_name;