SQL String Functions

SQL > SQL String Functions

In this section, we cover common string functions seen in SQL. Different RDBMS may employ different string functions, and there may also be differences in the syntax for each RDBMS even when the function call is the same. All such differences are explained in each section. Please note that we do not list all possible SQL string functions in this tutorial. Rather, the most commonly used ones are covered.

• SQL CAST
• SQL CONVERT
• SQL CONCATENATE
• SQL SUBSTRING
• SQL INSTR
• SQL TRIM
• SQL LENGTH
• SQL REPLACE
• SQL TO_DATE

SQL > SQL String Functions > CAST Function

In many cases, a database will automatically convert one data type into another when needed. On the other hand, there are instances when that is not the case, or when you want to explicitly specify what data type to change into. In these cases, you can use the CASTfunction. The syntax of the CAST function is as follows:

CAST (expression AS [data type])

where [data type] is a valid data type in the RDBMS you are working with.

Let's go through some examples to see how the CAST function is used. Assume we have the following table:

Table Student_Score

Column Name	Data Type
StudentID	integer
First_Name	char(20)
Score	float

and this table contains the following rows:

Table Student_Score

StudentID	First_Name	Score
1	Jenny	85.2
2	Bob	92.5
3	Alice	90
4	James	120.1

Example 1

SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score;

Result:

First_Name	Int_Score
Jenny	85
Bob	92
Alice	90
James	120

In Example 1, we use the CAST function to convert the Score column from type FLOAT to INTEGER. When we do this, different RDMBS have different rules on how to handle the numbers after the decimal point. In the above example, we assume that the numbers after the decimal point are always truncated.

Example 2

SELECT First_Name, CAST(Score AS char(3)) Char_Score FROM Student_Score;

Result:

First_Name	Char_Score
Jenny	85.
Bob	92.
Alice	90
James	120

In Example 2, we use the CAST function to convert the SCORE column from type FLOAT to CHAR(3). When we do this, we only take the first 3 characters. So, if there are more than 3 characters, everything after the first 3 characters is discarded.

SQL > SQL String Functions > CONVERT FunctionIn MySQL and SQL Server, the CONVERT function is very similar to the CAST function in that they both change the value from one data type to another. In this scenario, the syntax of theCONVERT function is as follows: CONVERT (expression, [data type]) where [data type] is a valid data type in the RDBMS you are working with. Let's go through an example to see how the CONVERT function is used. Let's assume we have the following table: Table Student_Score Column Name Data Type StudentID integer First_Name char(20) Score float and this table contains the following rows: Table Student_Score StudentID First_Name Score 1 Jenny 85.2 2 Bob 92.5 3 Alice 90 4 James 120.1 Example SELECT First_Name, CONVERT(Score, Integer) Int_Score FROM Student_Score; Result: First_Name Int_Score Jenny 85 Bob 92 Alice 90 James 120 In Oracle, the CONVERT function is used differently. It converts a string from one character set to another. Its syntax is: CONVERT (string, [new character set], [original character set])

SQL > SQL String Functions > Concatenate

Sometimes it is necessary to combine together (concatenate) the results from several different fields. Each database provides a way to do this:

• MySQL: CONCAT( )
• Oracle: CONCAT( ), ||
• SQL Server: +

The syntax for CONCAT( ) is as follows:

CONCAT (str1, str2, str3, ...)

The above syntax concatenates str1, str2, str3, and any other strings together. Please note the Oracle CONCAT( ) function only allows two arguments -- only two strings can be put together at a time using this function. However, it is possible to concatenate more than two strings at a time in Oracle using '||'.

Let's look at some examples. Assume we have the following table:

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

Example 1

MySQL/Oracle: 

SELECT CONCAT(Region_Name, Store_Name) FROM Geography
WHERE Store_Name = 'Boston';

Result:

'EastBoston'

Example 2

Oracle: 

SELECT Region_Name || ' ' || Store_Name FROM Geography
WHERE Store_Name = 'Boston';

Result:

'East Boston'

Example 3

SQL Server: 

SELECT Region_Name + ' ' + Store_Name FROM Geography
WHERE Store_Name = 'Boston';

Result:

'East Boston'

SQL > SQL String Functions > Substring

The Substring function in SQL is used to return a portion of string. This function is called differently in different databases:

• MySQL: SUBSTR( ), SUBSTRING( )
• Oracle: SUBSTR( )
• SQL Server: SUBSTRING( )

The syntax for SUBSTRING is as follows (we will use SUBSTR( ) here):

SUBSTR (str, position, [length])

where position and length are both integers. This syntax means the following: Start with theposition-th character in string str, select the next >length characters.

In MySQL and Oracle, length is an optional argument. When length is not specified, the entire string starting from the position-th character is returned. In SQL Server, length is required.

SUBSTR() can be used in SELECT, WHERE, and ORDER BY clauses.

Below are some examples. Assume we have the following table:

Table Geography

Region_Name	Store_Name
East	Chicago
East	New York
West	Los Angeles
West	San Diego

Example 1

SELECT SUBSTR (Store_Name, 3)
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

SUBSTR (Store_Name, 3)

s Angeles

Example 2

SELECT SUBSTR (Store_Name, 2, 4)
FROM Geography
WHERE Store_Name = 'San Diego';

Result:

SUBSTR (Store_Name, 2, 4)

an D

Example 3

SELECT Store_Name
FROM Geography
ORDER BY SUBSTR (Store_Name, 2, 4);

Result:

Store_Name

San Diego

New York

Chicago

Los Angeles

In this example, the ORDER BY criteria is based on the 2nd to 5th characters in the string. Below are the 2nd to 5th characters for each Store_Name:

Store_Name	SUBSTR(Store_Name,2,4)
Chicago	hica
New York	ew Y
Los Angeles	os A
San Diego	an D

Based on the above, we can see that 'San Diego' would be first, followed by 'New York', 'Chicago', and finally 'Los Angeles.'

SQL > SQL String Functions > INSTR Function

The INSTR function in SQL is used to find the starting location of a pattern in a string. This function is available in MySQL and Oracle, though they have slightly different syntaxes:

The syntax for the Length function is as follows:

MySQL:

INSTR (str, pattern)

Find the staring location of pattern in string str.

Oracle:

INSTR (str, pattern, [starting position, [nth location]])

Find the staring location of the nth occurrence of pattern beginning in the starting position-th position in string str.

Let's take a look at some examples. Assume we have the following table:

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

Example 1 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'o')
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

2

Example 2 (both Oracle and MySQL)

SELECT INSTR (Store_Name, 'p')
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

0

In this case, the pattern p does not exist in string 'Los Angeles', so the function returns 0.

Example 3 (Oracle only)

SELECT INSTR(Store_Name,'e', 1, 2)
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

10

The second occurrence of e is in the 10th position.

SQL > SQL String Functions > Trim

The TRIM function in SQL is used to remove specified prefix or suffix from a string. The most common pattern being removed is white spaces. This function is called differently in different databases:

• MySQL: TRIM( ), RTRIM( ), LTRIM( )
• Oracle: RTRIM( ), LTRIM( )
• SQL Server: RTRIM( ), LTRIM( )

The syntax for these trim functions are:

TRIM( [ [LOCATION] [remstr] FROM ] str)

[LOCATION] can be either LEADING, TRAILING, or BOTH. This function gets rid of the [remstr] pattern from either the beginning of the string or the end of the string, or both. If no [remstr] is specified, white spaces are removed.

LTRIM (str)

LTRIM Removes all white spaces from the beginning of the string.

RTRIM (str)

RTRIM emoves all white spaces at the end of the string.

Example 1

SELECT TRIM('   Sample   ');

Result:

'Sample'

Example 2

SELECT LTRIM('   Sample   ');

Result:

'Sample   '

Example 3

SELECT RTRIM('   Sample   ');

Result:

'   Sample'

SQL > SQL String Functions > Length Function

The Length function in SQL is used to get the length of a string. This function is called differently for the different databases:

• MySQL: LENGTH( )
• Oracle: LENGTH( )
• SQL Server: LEN( )

The syntax for the Length function is as follows:

Length (str)

The above finds the length of the string str.

Let's take a look at some examples. Assume we have the following table:

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

Example 1

SELECT Length (Store_Name)
FROM Geography
WHERE Store_Name = 'Los Angeles';

Result:

Length (Store_Name)

11

Example 2

SELECT Region_Name, Length (Region_Name)
FROM Geography;

Result:

Region_Name	Length (Region_Name)
East	4
East	4
West	4
West	4

SQL > SQL String Functions > Replace Function

The Replace function in SQL is used to update the content of a string. The function call isREPLACE( ) for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:

Replace (str1, str2, str3)

In str1, find where str2 occurs, and replace it with str3.

Assume we have the following table:

Table Geography

Region_Name	Store_Name
East	Boston
East	New York
West	Los Angeles
West	San Diego

If we apply the following Replace function:

SELECT REPLACE (Region_Name, 'ast', 'astern') REGION1
FROM Geography;

Result:

REGION1

Eastern

Eastern

West

West