MySQL provided various functions to interact and modify the strings passed via arguments or fetched from tables. This article will go through all the available MSQL String Functions. Before that, let’s understand how exactly a string is treated in MySQL.
Table of Contents
MySQL String
When a binary string is passed to a string function as an argument, the result is also a binary string. A binary string is a number that has been transformed into a string. Only comparisons are affected by this.
If any expression in a string comparison is case-sensitive, the comparison is normally conducted in that manner.
Binary strings are shown using hexadecimal notation whenever a string function is called from within the MySQL client, depending on the value of the — binary-as-hex option.
String CONCAT_WS() function
CONCAT WS() is a special form of CONCAT that stands for Concatenate With Separator (). The first argument serves as a divider between the other arguments. Between the strings to be concatenated, a separator is added. Like the rest of the inputs, the separator can be a string. The result is NULL if the separator is NULL.
mysql> SELECT CONCAT_WS(‘,’,’First name’,’Second name’,’Last Name’);
Output: ‘First nameSecond nameLast Name’
String CONCAT() function
Concatenates the parameters and returns the resultant string. There could be one or many arguments. The result is a nonbinary string if all parameters are nonbinary strings. The result is a binary string if any of the arguments are binary strings. The comparable nonbinary string form of a numeric argument is converted.
If any of the arguments are NULL, CONCAT() returns NULL.
mysql> SELECT CONCAT(‘My’, ‘S’, ‘QL’);
Output: ‘MySQL’
String CHARACTER_LENGTH() function
The length of the string str in code points is returned. A single code point is assigned to a multibyte character. This means that LENGTH() returns 6 for a string containing two 3-byte characters, whereas CHAR LENGTH() returns 2, as illustrated here:
mysql> SET @dolphin:=’海豚’;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+——————+———————–+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+——————+———————–+
| 6 | 2 |
+——————+———————–+
1 row in set (0.00 sec)
String ELT() function
MySQL’s Elt() function is a Sring function. This method returns the Nth entry from a string list.
Example
select elt(3,’this’,’is’,’GoCoding’,’website’)
Output:
GoCoding
String EXPORT_SET() function
MySQL’s export set() function is a String function. For each bit set, this procedure returns a string.
select export_set(5, ‘Yes’,’No’,’,’,4);
Output:
Yes,No,Yes,No
String FIELD() function
MySQL’s Field(str,str1,str2,str3,…) function is a Sring function. This method returns the string’s index.
Select field(‘a’,’b’,’c’,’d’,’a’);
Output:
4
String FIND_IN_SET() function
MySQL’s Find in set(str,strlist) function is a Sring function. The value of the string in the first position in the argument is returned by this method.
Select find_in_set(‘n’,’gocoding’);
Output:
7
String FORMAT() function
MySQL’s String function Format(X,D[,locale]) is a String function. This approach rounds the number X to the decimal place of D.
Select format(12345.6789,3);
Output:
12,345.679
String FROM_BASE64() function
MySQL’s String function Format(X,D[,locale]) is a String function. This approach rounds the number X to the decimal place of D.
Select from_base64(‘java’) as ‘Input’;
Output:
String HEX() function
MySQL’s Hex() function is a Sring function. This method returns the supplied integer or String as a hexadecimal string.
Select hex(‘a’);
Output:
61
String INSERT() function
MySQL’s INSERT(str,pos,len,newstr) function is a Sring function. A string str is supplied to this function with a position ‘pos’ that specifies where the character should be placed and a length ‘len’ that specifies the length of the character to be placed, i.e. string newstr.
Select insert(‘GoCoding’,5,1,’t’);
Output:
‘GoCoding’
String INSTR() function
INSTR(str,substr) is a MySQL Sring function. This method returns the substr’s (string 2) first occurrence in string str (string 1).
Select instr(‘GoCoding’,’Code’);
Output:
3
String LCASE() function
LCASE(str) is a MySQL String function. This method returns a lowercase string.
select lcase(‘GOCODING’);
Output:
gocoding
Note: The function LOWER() is similar to this function. We will discuss it too later.
String LEFT() function
select left(‘GoCoding’,2);
Output:
Go
String LENGTH() function
LENGTH(str) is a MySQL String function. The length of the given string?str? in bytes is returned by this method.
select LENGTH(GoCoding);
Output:
8
String like() function
Select ‘GoCoding’ like ‘gocoding’;
Output:
1
String LOAD_FILE() function
MySQL’s LOAD FILE(file name) function is a String function. This method returns the file’s content. It returns NULL if the file does not exist or cannot be read.
String LOCATE() function
LOCATE(substr,str) is a MySQL Sring function. The first occurrence of the supplied substr in the given string is returned by this method.
select locate(‘Coding’, ‘GoCoding’);
Output:
6
String LOWER() function
MySQL’s LOWER(str) function is a Sring function. The specified string is returned in lower case by this method.
Select lower(‘GOCODING’);
Output:
‘gocoding’
String LPAD() function
MySQL’s LPAD(str,len,padstr) function is a Sring function. This method returns the string’str,’ padded to the specified length.
Select lpad(‘gocoding’, 2, ‘**’);
Output:
go
String LTRIM() function
LTRIM(str) is a MySQL String function. This method removes the leading space from the string and returns it.
Select ltrim(‘ GoCoding’);
Output:
GoCoding
String MAKE_SET() function
MySQL’s MAKE SET(bits,str1,str2,…) function is a Sring function. For the provided bit, this method retrieves values from set.
Select make_set(1, ‘apple’,’mango’,’grapes’);
Output:
apple
String MID() function
MySQL’s MID(str,pos,len) function is a Sring function. This method returns a string with the length and position you specify.
Select mid(‘mysql’, 3, 4);
Output:
sql
String OCTET_LENGTH() function
OCTET LENGTH(str) is a MySQL Sring function. The length of a string is returned by this method.
select octet_length(‘GoCoding’);
Output:
10
String OCT() function
MySQL’s Oct(n) function is a Sring function. The octal value of a given number is returned by this procedure (n).
select oct(10);
Output:
12
String ORD() function
ORD(str) is a MySQL Sring function. The code for the given string is returned by this procedure.
Select ord(‘mysql’);
Output:
109
String POSITION() function
MySQL’s POSITION(substr IN str) function is a String function. This function returns the substring’s position in the string.
select position(‘point’ in ‘GoCoding’);
Output:
6
String QUOTE() function
MySQL’s String function quotes(str). The string supplied in single quotes is returned by this procedure.
select quote(‘GoCoding is a website’);
Output:
‘GoCoding is a website’
String REPEAT() function
REPEAT(str,count) is a MySQL String function. The string reputation counter times is returned by this procedure.
select repeat(‘GoCoding ‘, 4);
Output:
GoCoding GoCoding GoCoding GoCoding
String REPLACE() function
MySQL’s REPLACE(str,from str,to str) function is a String function. This method converts a string into a string from a specified string.
Syntax: REPLACE(str_which_needs_replacement,str_that_will_be_replaced,str_that_will_be_replacing)
select replace(‘XYZ’, ‘X’, ‘A’);
Output:
AYZ
String REVERSE() function
MySQL’s REVERSE() function is a String function. This method reverses the order of the given string.
Select reverse(‘don’);
Output:
nod
String RIGHT() function
MySQL’s Right() function is a String function. This method returns the ability to extract a substring from a string, starting from the right side.
Select right(‘GoCoding’, 6);
Output:
Coding
String RPAD() function
The RPAD() is a String function of MySQL. This method returns the given string which is right-padded with the string padstr to the given length (len).
Select rpad(‘GoCoding’, 4, ‘**’);
Output:
GoCo
String RTRIM() function
MySQL’s Rtrim() function is a String function. If there is any extra space in the string’s tail, this technique removes it.
Select rtrim(‘GoCoding ‘);
Output:
GoCoding
String SOUNDEX() function
MySQL’s Soundex() function is a String function. A Soundex string is returned by this procedure. Soundex strings should be identical for two strings that sound almost identical.
Select soundex(‘GoCo’);
Output:
3131
String SPACE() function
In MySQL, the SPACE() method returns a string containing the provided empty space characters.
SELECT ‘start’, SPACE(10), ‘end’;
Output:
+——-+————+—–+
| start | SPACE(10) | end |
+——-+————+—–+
| start | | end |
+——-+————+—–+
String STRCMP() function
Required to The strings that will be compared
SELECT STRCMP(“SQL Tutorial”, “SQL Tutorial”);
Output:
0
String SUBSTR() function
Remove a substring from a string (start at position 5 and remove 3 characters):
SELECT SUBSTR(“SQL Tutorial”, 5, 3) AS ExtractString;
| ExtractString |
| Tut |
String SUBSTRING_INDEX() function
| SUBSTRING_INDEX(“www.gocoding.org”, “.”, 1) |
| www |
Before a specified number of delimiters, return a substring of a string:
String SUBSTRING() function
Start at position 5 and extract 3 characters from a string:
SELECT SUBSTRING(“SQL Tutorial”, 5, 3) AS ExtractString;
Output:
Tut
String Trim() function
To remove leading and trailing spaces from a string, follow these steps:
| TrimmedString |
| SQL Tutorial |
String UCASE() function
Covert input string into uppercase
SELECT UCASE(“SQL Tutorial is FUN!”) AS UppercaseText;
Output:
SQL TUTORIAL IS FUN!
String UNHEX() function
The UNHEX() function in MySQL is used to convert a Hexadecimal number into bytes that can be represented by a Number. It returns a binary string as a result.
SELECT UNHEX(‘48656C6C6F’) AS String_Name;
Output:
String_Name
Hello
String UPPER() function
Convert the string function into upper case
SELECT UPPER(“SQL Tutorial is FUN!”);
Output:
SQL TUTORIAL IS FUN!
0 Comments