Video Tutorial 9 SQL Functions: left, substring, concat, upper, lower mysql workbench

SQL has many built-in functions. The names of the functions may change from one database to another, but the funcionality remains the same.

SQL LEFT FUNCTION

Returns the left part of a character string with the specified number of characters.

SQL LEFT FUNCTION SYNTAX

LEFT ( character_expression , integer_expression )

1. Character_expression is an expression of character or binary data. character_expression can be a constant, variable, or column.
2. Integer_expression is a positive integer that specifies how many characters of the character_expression will be returned.

SQL LEFT FUNCTION EXAMPLE

As we can see in our table "books" below, the authors of our books don´t always start with a capital letter. In this example we are going to change this, so that all the names of the authors start with a capital letter.

Here we have our column "author";

Firstly, we are going to separate the first letter of the column "author", of our table "books", from the rest of the word. For this we use the function LEFT(), which bringings back an "e";

SQL UPPER FUNCTION

The UPPER() function converts the value of a field to uppercase.

SQL UPPER FUNCTION SYNTAX

SELECT UPPER(column_name) FROM table_name;

SQL UPPER FUNCTION EXAMPLE

Once we have the first letter of the name of the author, we want to write it in capital letters with the instruction;

SQL SUBSTRING FUNCTION

The SUBSTRING() function returns the substring as specified.

SQL SUBSTRING FUNCTION SYNTAX

SUBSTRING (string, 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.

SQL SUBSTRING FUNCTION EXAMPLE

Here we can see that the function returns all the string of the author from the second letter onwards:

SQL LOWER FUNCTION

The LOWER() function returns a character expression after converting uppercase character data to lowercase.

SQL LOWER FUNCTION SYNTAX

SELECT LOWER(column_name) FROM table_name;

SQL LOWER FUNCTION EXAMPLE

From the beginning of the exercise we are trying to obtain the names of the authors with a capital letter and the rest of the word in lower case. We already have the first letter in capital letters and now we want the rest of the word in lower case, for this we can use the following sentence:

SQL CONCAT FUNCTION

Returns a string that is the result of concatenating two or more string values.

SQL CONCAT FUNCTION SYNTAX

CONCAT ( string_value1, string_value2 [, string_valueN ] );

SQL CONCAT FUNCTION EXAMPLE

What we need now is to join the first letter with the rest of the name. For this we can use the function CONCAT to join the first letter of the column author in capital letters and the rest of the word in lower case; And the result is the complete name of the authors, written correctly.

<< Previous

Next >>