SQL String Functions (Part 1)

Tram Ho

In this article, I would like to introduce some common string functions in SQL. Each RDBMS may use different string functions, but the syntax of the functions may be different for each RDBMS even though their names are the same.

1. CAST

The CAST function in SQL allows to convert data from one data type to another. For example, we can use the CAST function to convert numeric data to text.

Syntax

For example

In this example we use the StudentScore table below.

StudentIDFirst_NameScore
firstJenny85.2
2Bob92.5
3Alice90
4James120.1
  • Example 1:

Result:

In this example 1, we used the CAST function to convert the Score column data from FLOAT to INTEGER.

  • Example 2:

Result:

In this example 2, we used the CAST function to convert the Score column data from FLOAT to CHAR (3). Since only the first 3 characters are taken, if there are more than 3 characters, the characters after the first 3 characters will be omitted.

2. CONCATENATE

The Concatenate function is used to combine strings of characters together. Each DB has its own way of implementing this function:

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

Syntax

* For Oracle, the CONCAT () function only allows 2 arguments, but it can combine more than 2 strings with syntax ‘||’

For example

In this example we use the following Geography table:

Region_NameStore_Name
EastBoston
EastNew York
WestLos Angeles
WestSan Diego
  • Example 2: Using the CONCAT () function

MySQL / Oracle

Result:

  • Example 1: Using ‘||’

Oracle:

Result:

  • Example 3: Using ‘+’

SQL Server:

Result:

3. SUBSTRING

The Substring function is used to return part of the input string. Each DB has its own way of implementing this function.

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

Syntax

In which, position and length are both integer. The above syntax has the meaning of retrieving the string of length from the position of the string str .

In MySQL, length is an optional parameter, but in Oracle, length is required.

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

For example

In this example we use the following Geography table

Region_NameStore_Name
EastChicago
EastNew York
WestLos Angeles
WestSan Diego
  • Example 1

Result:

  • Example 2

Result:

4.TRIM

The Trim function in SQL is used to remove prefixes and suffixes in string. In which, the most often removed pattern is a space (white space). For different DBs this function is also called differently:

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

Syntax

  • The syntax of the TRIM function

  • The syntax of the LTRIM function

LTRIM removes all leading spaces

  • The syntax of the RTRIM function

RTRIM removes all trailing spaces

For example

  • Example 1: TRIM function

Result:

  • Example 2: LTRIM function

Result:

  • Example 3: RTRIM function

Result:

5. LENGTH

The Length function is used to return the length of a string. This function has different names in different DBs:

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

Syntax

Example In this example we use the Geography table below

Region_NameStore_Name
EastChicago
EastNew York
WestLos Angeles
WestSan Diego
  • Example 1

Result:

  • Example 2

Result:

References

https://www.1keydata.com/sql/sql-string-functions.html

Share the news now

Source : Viblo