Tuesday 3 December 2019

How to extract Mysql substring?

In MySQL we have many functions to manipulate strings: calculate their length, extract a fragment located to the right, to the left or in any position, eliminate final or initial spaces, and convert to hexadecimal and binary, among others. In this article, we are going to focus on the functions that allow us the extraction of substrings.
Here is a list of functions that allow us to do extractions of substrings in Mysql:
  • SUBSTRING
  • SUBSTR
  • MID
  • INSTR
  • LEFT
  • RIGHT
We will discover what each of these functions does by looking at its theoretical definition and also with usage examples.

Example

 We will be using a users table with the following content:

example users table
SUBSTRING

Definition:

We can use the function SUBSTRING() to extract a substring from a string. We have to indicate the starting point and the maximum length of the substring that we want to extract.

Input parameters:

inout SUBSTRINGSUBSTRING

Syntax:

Option 1: 
  1. SUBSTRING(param_string, param_start, param_length)
Option 2: 
  1. SUBSTRING(param_string FROM param_start FOR param_length)

Example:

Extract a substring from the text in a column (start at position 2, extract 5 characters):
  1. SELECT SUBSTRING(user, 2, 5) AS ExtractString
  2. FROM users;
Extracted Substring

SUBSTR AND MID

Definition:

SUBSTR() and MID() functions are synonyms for SUBSTRING(). They work in the same way and uses the same parameters.

Input parameters:

Syntax:

Option 1: 
  1. SUBSTR(param_string, param_start, param_length)
Option 2: 
  1. SUBSTR(param_string FROM param_start FOR param_length)

Example:

Extract a substring from the text in a column (start at position 2, extract 5 characters):
  1. SELECT SUBSTR(user, 2, 5) AS ExtractString
  2. FROM users;
SELECT SUBSTRINSTR

Definition:

This function gets the position of the first occurrence of a string in another (case-insensitive search).

Input parameters:

Input parametersSyntax:

  1. INSTR(param_string, param_substring)

Examples:

  1. Search for the substring “IS” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"
param_substring = “IS”

MySQL Query:

  1. SELECT INSTR("THIS IS A TEST", "IS") AS MatchPosition;

Result:

MatchPosition = 3 (THIS IS A TEST)
  1. Search for the substring “A” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"
param_substring = “IS”

MySQL Query:

  1. SELECT INSTR("THIS IS A TEST", "A") AS MatchPosition

Result:

MatchPosition = 9 (THIS IS A TEST)
  1. Search for the substring “MY” in the string “THIS IS A TEST”:

Parameters:

param_string = "THIS IS A TEST"
param_substring = “MY”

MySQL Query:

  1. SELECT INSTR("THIS IS A TEST", "MY") AS MatchPosition

Result:

MatchPosition = 0 because no match was found

LEFT

Definition:

Extracts a number of characters from a string starting from the left side. The number of characters to be returned is indicated by a parameter.
Parameters:

LEFT ParametersSyntax:

  1. LEFT(param_string, param_number_of_chars)

Example:

Extract the initial of the name of each user
SELECT LEFT(name, 1) AS ExtractString FROM users;
SELECT LEFT

RIGHT

Definition:

This function extracts a number of characters from a string starting from the right side. The number of characters to be returned is indicated by a parameter.

Parameters:

ParametersSyntax:
  1. RIGHT(param_string, param_number_of_chars)

Examples:

Extract the last letter of the name of each user
SELECT RIGHT(name, 1) AS ExtractString FROM users;
SELECT RIGHT

0 comments:

Post a Comment