Using SQL String Functions (USSF)

1. CONCAT(str1, str2, ...)

This function is used to return a string resulting from concatenating (combining 2 or more) arguments. Can consist of one or more arguments. If all arguments are nonbinary strings, then the result is also a nonbinary string. If the arguments include binary strings, then the result is a binary string. Numeric arguments are converted to their nonbinary equivalent string form.


MYSQL String Functions

Briefly, Achmad Solichin explains in his book entitled MySQL5 From Beginner to Advanced about CONCAT, which is a function used to combine two or more strings (columns).

In this discussion, I will use the NORTHWIND database to demo some examples of its functions.

Example:

To show you how it works, I will first select a few columns of the customers table as part of this experiment, so you will know the difference before and after CONCAT.

BEFORE:

SELECT CompanyName,CustomerID,Address,PostalCode FROM customers;

Output:


Before CONCAT

AFTER:

SELECT CompanyName,CustomerID,
CONCAT (Address,PostalCode) AS Hasil_CONCAT_FullAddress
FROM customers;

Output:


After CONCAT

2. CONCAT_WS (separator, str1, str2, ...)

It stands for Concatenate With Separator and is a special formula of the CONCAT() form. The first argument is the separator, while the rest are regular arguments. The separator will be added between the intersection of the strings. The separator can be a string or it can be NULL.

Meanwhile, Achmad Solichin explains in his book briefly, where this function is used to combine two or more strings (columns) with a separator between each string.

Example:

As usual, I will select the tables that will be used for the experiment first, then I will show the changes. In this case I will use the employees table for the experiment.

BEFORE:

SELECT TitleOfCourtesy, FirstName, LastName AS FullName
FROM employees

Output:


Before CONCAT_WS

AFTER:

With NULL Separator,

SELECT CONCAT_WS('',TitleOfCourtesy,FirstName,LastName) AS FullName
FROM employees

Output:


CONCAT_WS with NULL Separator

With SPACE Separator,

SELECT CONCAT_WS(' ',TitleOfCourtesy,FirstName,LastName) AS FullName
FROM employees

Output:


CONCAT_WS with SPACE Separator

With STRING Separator,

SELECT CONCAT_WS(' + ',TitleOfCourtesy,FirstName,LastName) AS FullName
FROM employees

Output:


CONCAT_WS with STRING Separator

With ARGUMENT Separator,

SELECT CONCAT_WS(TitleOfCourtesy,TitleOfCourtesy,FirstName,LastName)
AS FullName FROM employees

Output:


CONCAT_WS with ARGUMENT Separator

3. MID() or SUBSTR() or SUBSTRING()

MID() is a synonym of SUBSTR() is a synonym of SUBSTRING(). The MID() / SUBSTR() / SUBSTRING() format without the len (length) argument will return the value of the str (string) argument starting from the pos (position) argument. While the format with the len (length) argument will return the value of the str (string) argument with the character length of the len (length) argument, and starting from the pos (position) argument.

Meanwhile, Achmad Solichin briefly explained that this function is used to take or cut a string starting from the initial character as many characters as the length. As a note that in MySQL, the string index starts with 1, not 0.

Standard Format of SUBSTR or SUBSTRING Argument:

/*--Format Standar MID || SUBSTR || SUBSTRING--*/

MID(str,pos)
MID(str FROM pos)
MID(str,pos,len)
MID(str FROM pos FOR len)

SUBSTR(str,pos)
SUBSTR(str FROM pos)
SUBSTR(str,pos,len)
SUBSTR(str FROM pos FOR len)

SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)

Example:

As usual, I will select the table first for the experimental material, and the choice fell on the employees table, FirstName column.

BEFORE:

SELECT FirstName FROM employees;

Output:


Before SUBSTR or SUBSTRING

AFTER:

MID()

SELECT FirstName AS ASLINYA,
MID(FirstName,2) AS CONTOH_1,
MID(FirstName FROM 3) AS CONTOH_2,
MID(FirstName,2,3) AS CONTOH_3,
MID(FirstName FROM 2 FOR 3) AS CONTOH_4
FROM employees;

Output:


After MID()

SUBSTR()

SELECT FirstName AS ASLINYA,
SUBSTR(FirstName,2) AS CONTOH_1,
SUBSTR(FirstName FROM 3) AS CONTOH_2,
SUBSTR(FirstName,2,3) AS CONTOH_3,
SUBSTR(FirstName FROM 2 FOR 3) AS CONTOH_4
FROM employees;

Output:


After SUBSTR()

SUBSTRING()

SELECT FirstName AS ASLINYA,
SUBSTRING(FirstName,2) AS CONTOH_1,
SUBSTRING(FirstName FROM 3) AS CONTOH_2,
SUBSTRING(FirstName,2,3) AS CONTOH_3,
SUBSTRING(FirstName FROM 2 FOR 3) AS CONTOH_4
FROM employees;

Output:


After SUBSTRING()

4. LENGTH() or OCTET_LENGTH() or CHAR_LENGTH() or CHARACTER_LENGTH()

Returns the length of the string str (string), in bytes. More than one character is called multiple bytes. This means that a string containing five characters is 2-bytes long, so LENGTH() will return 10 bytes, while CHAR_LENGTH() will return 5.

Example:

SELECT
LENGTH('contoh'),
OCTET_LENGTH('contoh'),
CHAR_LENGTH('contoh'),
CHARACTER_LENGTH('contoh');

Output:


LENGTH() Family

5. LEFT(str, len)

Used to return the len/length of the leftmost character of a str/string. Or NULL if the argument is NULL, even SPACE.

Meanwhile, Achmad Solichin explains in his book that this function is used to cut a string from the left by the number of characters long.

Example:

First I selected a phone in the shippers table as a test subject.

BEFORE:

SELECT Phone FROM shippers

Output:


Before LEFT(str, len)

AFTER:

SELECT LEFT(Phone,7) FROM shippers

Output:


After LEFT(str, len)

6. RIGHT(str, len)

It is the opposite of LEFT(), which is used to return the len/length of the rightmost character of a str/string. Or NULL if the argument is NULL, even SPACE.

Meanwhile, Achmad Solichin explains in his book that this function is used to cut a string from the right by the length of the character.

Example:

Still using the example above, the phone shippers column, so the output will be like this:

SELECT RIGHT(Phone,5) FROM shippers

Output:


After RIGHT(str, len)

7. LTRIM(str)

This function is useful for returning a string followed by the removal of spaces starting from the left.

Meanwhile, Achmad Solichin explained that this function is used to remove spaces at the beginning of a string (left).

Example:

SELECT LTRIM('    barbarian');

Output:


After LTRIM(str) Function

8. RTRIM(str)

It is the opposite of LTRIM(str), that is, it is useful for returning a string followed by the removal of spaces starting from the right.

Meanwhile, Achmad Solichin explained that this function is used to remove spaces at the end of the string (right).

Example:

SELECT RTRIM('    barbarian      ');

Output:


After RTRIM(str) Function

9. TRIM(str)

It is a useful function to return a string followed by the removal of spaces on both the right and left.

Meanwhile, Achmad Solichin explained that this function is used to remove spaces at the beginning and end of a string (left and right).

TRIM() Argument Format - #Optional

This argument is optional, so if it is not included, by default the TRIM() function will remove spaces on both the left and right.

TRIM( [BOTH] | [LEADING] | [TRAILING] [remstr] [FROM] str)
atau
TRIM([remstr] [FROM] str)

Information:

  • | = Read or
  • [] = As argument limit

For more details on how to use it, you can see the example below!

Example:

SELECT
TRIM('  barbarian pekka archer   '),
TRIM(LEADING 'x' FROM 'xxxbarxxx'),
TRIM(BOTH 'x' FROM 'xxxbarxxx'),
TRIM(TRAILING 'xyz' FROM 'barxxyz');

Output:


After TRIM()

10. REPLACE(str, from_str, to_str)

This function is useful for returning str(string) with the string condition specified with from_str, and replaced by to_str. REPLACE() is case-sensitive, meaning the condition in the argument from_str or to_str is very much considered between CAPITAL or small letter.

Meanwhile, Achmad Solichin explained that this function is used to replace one string with another string.

Example:

SELECT REPLACE ('www.gatewan.com', 'w', 'x' );

Output:


After REPLACE()

11. REPEAT(str, count)

Useful for repeating a string a certain number of times.

This function is useful for returning a string consisting of str(string), repeated in a certain count(number of times). If count is less than 1, then it will be returned with an empty string. So, REPEAT() will only return NULL if one (count) or both of its arguments are NULL.

Meanwhile, Achmad Solichin explained that this function is used to duplicate a string a certain number of times.

Example:

SELECT
REPEAT('MySQL', 3),
REPEAT('MySQL', 0);

Output:


After REPEAT()

12. REVERSE(str)

Reverses the characters of a string.

This function is useful for returning str(string) with reverse character order.

Meanwhile, Achmad Solichin explained that this function is used to reverse a string.

Example:

SELECT
REVERSE('una'),
REVERSE('edeg');

Output:


After REVERSE()

13. LCASE(str)

Lower Case stands for Lower Case, which is a synonym for LOWER(), useful for returning str(string) by changing it to lowercase according to its character mapping set. By default it is latin1 (1cp1252 Western Europe).

Meanwhile, Achmad Solichin explained that this function is used to change strings to lowercase.

Example:

SELECT
LOWER('GATEWAN'),
LCASE('GATEWAN');

Output:


After LCASE() and LOWER()

14. UCASE(str)

Upper Case stands for, which is a synonym for UPPER(). useful for returning str(string) by changing it to uppercase (CAPITAL) according to its character mapping set. By default it is latin1 (1cp1252 Western Europe).

Meanwhile, Achmad Solichin explained that this function is used to change strings to uppercase.

Example:

SELECT
UPPER('wawan beneran'),
UCASE('wawan beneran');

Output:


After UPPER() and UCASE()

Reference:

String Search SQL Example

In order to continue the  previous material .


String Search in SQL

Searching records with certain criteria can use the LIKE clause. The LIKE operator is an accurate search, meaning that the text entered must be complete and in accordance with the available data. But it doesn't matter with upper or lower case letters. Example:

SELECT * FROM products WHERE ProductName LIKE 'Konbu';


SQL Search Using LIKE

SQL Search Operators

This operator is very useful for searching data that contains a stated value. For example, to search for patient data that contains the name 'Andi' or contains the letter 'J'. The data searched with this operator produces quite accurate results and is very noticeable in presenting information or reports.

To use this operator you need to add a wildcard ( _ ) or a percent sign ( % ). For the sign ( _ ) means any character. For example, a_u matches abu, alu, anu or awu. But does not match abru, altru or accu. While the % sign means it matches any character and any number, including the zero character. Uppercase or lowercase letters are considered the same. You can see the format in the table below:

Use of the % sign

| Bentuk | Keterangan                                                                                 |
|--------|--------------------------------------------------------------------------------------------|
| %h%    | Cocok dengan karakter apa saja yang mengandung karakter h.                                 |
| %h     | Cocok dengan karakter yang berakhiran h. Bentuk ini hanya berlaku untuk tipe data VARCHAR. |
| h%     | Cocok dengan karakter yang berawalan h.                                                    |

Unlike the previous example which is accurate by only using the LIKE operator, the inclusion of the % sign is useful for conducting a precise search, meaning how good the level of search precision is determined by the text entered. Example:

I want to find a product called "Malacca Sugar".

1. Low Precision Search:

SELECT * FROM products WHERE ProductName LIKE '%gu%';


SQL Search With Low Precision Level

2. High Precision Search:

SELECT * FROM products WHERE ProductName LIKE '%gul%';


SQL Search With High Precision Level


Post a Comment

Previous Next

نموذج الاتصال