The CAST function in SQL converts data from one data type to another.
For
example, we can use the CAST function to convert numeric data into character
string data.
Syntax
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.
Examples
We use the following table for our examples.
Table Student_Score
Column Data
Name Type
StudentID integer
First_Name char(20)
Score float
This table contains the following rows:
Table Student_Score
Student First_Na Scor
ID me e
1 Jenny 85.2
2 Bob 92.5
3 Alice 90
xample 1
SELECT First_Name, CAST(Score AS Integer) Int_Score FROM Student_Score;
Result:
First_Na Int_Sco
me re
Jenny 85
Bob 92
Alice 90
James 120
Example 2
SELECT First_Name, CAST(Score AS char(3)) Char_Score FROM Student_Score;
Result:
First_Na Char_Sco
me re
Jenny 85.
Bob 92.
Alice 90
James 120
CONVERT Function
The CONVERT function in MySQL and SQL Server converts data from one data
type to another.
Syntax
The syntax of the CONVERT function is as follows:
CONVERT (expression, [data type])
where [data type] is a valid data type in the RDBMS you are working with.
Example
We use the following table for our example.
Table Student_Score
Column Data
Name Type
StudentID integer
First_Name char(20)
Score float
This table contains the following rows:
Table Student_Score
Student First_Na Scor
ID me e
1 Jenny 85.2
2 Bob 92.5
3 Alice 90
120.
4 James
1
The SQL statement,
SELECT First_Name, CONVERT(Score, Integer) Int_Score FROM Student_Score;
produces the following result set:
First_Na Int_Sco
me re
Jenny 85
Bob 92
Alice 90
James 120
CONVERT function in Oracle
In Oracle, the CONVERT function is used differently. It converts a string from
one character set to another.
Syntax in Oracle
CONVERT (string, [new character set], [original character set])
The Concatenate function combines multiple character strings together. Each database
provides its own way(s) to do this:
The syntax for CONCAT( ) is as follows:
CONCAT (str1, str2, str3, ...)
The above syntax concatenates str1, str2, str3, and any other strings together.
Each str can be a column name, or it can be a literal character string (meaning a
sequence of characters enclosed by two single quotes), or just white space.
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 '||'.
The syntax for using '||' to concatenate is as follows:
str1 || str2 || str3 ...
The syntax for using '+' to concatenate is as follows:
str1 + str2 + str3 ...
egion_Na Store_Na
me me
East Boston
East New York
Los
West
Angeles
West San Diego
Example 1: Use CONCAT function to concatenate
MySQL/Oracle:
SELECT CONCAT(Region_Name, Store_Name) FROM Geography
WHERE Store_Name = 'Boston';
Result:
'EastBoston'
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 the position-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.
Examples
We use the following table for our examples.
Table Geography
Region_Na Store_Na
me me
East Chicago
East New York
Los
West
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
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:
Syntax
The syntax for the INSTR function is as follows:
MySQL:
INSTR (str, pattern)
Find the staring location of pattern in string str.
Oracle:
INSTR (str, pattern, [starting position, [nth occurrence]])
Find the starting location of the nth occurrence of pattern beginning in the
starting position-th position in string str.
Examples
We use the following table for our examples.
Table Geography
Region_Na Store_Na
me me
East Boston
East New York
Los
West
Angeles
West San Diego
Example 1 (both Oracle and MySQL)
SELECT INSTR (Store_Name, 'o')
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:
The first occurrence of 'o' is the second character in the word 'Los Angeles.'
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.
rim
The TRIM function in SQL is used to remove a specified prefix or suffix from a
string. The most common pattern being removed is the white space. This
function is called differently in different databases:
he syntax for the TRIM function is as follows:
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.
The syntax for the LTRIM function is as follows:
LTRIM (str)
LTRIM removes all white spaces from the beginning of the string.
The syntax for the RTRIM function is as follows:
RTRIM (str)
RTRIM removes all white spaces at the end of the string.
Examples
Example 1: TRIM function
SELECT TRIM(' Sample ');
Result:
'Sample'
Example 2: LTRIM function
SELECT LTRIM(' Sample ');
Result:
'Sample '
Example 3: RTRIM function
SELECT RTRIM(' Sample ');
Result:
' Sample'
Length Function
The Length function in SQL is used to get the length of a string. This function
has a different name for different databases:
The syntax for the Length function is as follows:
Length (str)
The length of the string str is returned
SELECT Length (Store_Name)
FROM Geography
WHERE Store_Name = 'Los Angeles';
Result:
Length
(Store_Name)
11
Replace Function
The Replace function in SQL is used to update the content of a string. The
function call is REPLACE( ) for MySQL, Oracle, and SQL Server.
Syntax
The syntax of the Replace function is:
REPLACE (str1, str2, str3)
In str1, find where str2 occurs, and replace it with str3.
SELECT REPLACE (Region_Name, 'ast', 'astern') REGION1
FROM Geography;
we get the following result:
REGION
1
Eastern
Eastern
West
West