Post
Ask Question

String Functions in MySQL

INTRODUCTION

 
In this tutorial, I am going to explain about MySQL String Functions with examples. This article will cover the following topics. Let’s see.
  1. Introduction
  2. String Functions in MySQL
  3. Conclusion

STRING FUNCTIONS IN MYSQL

 

ASCII

 
This function returns ASCII code value of the leftmost character of the String “str” and returns 0 if the “str” is the empty string. And, it returns NULL if “str” is NULL.
 
Syntax
ASCII (str)
 
Example
  1. SELECT ASCII('0'),  
  2.     ASCII('A'),  
  3.     ASCII('a'),  
  4.     ASCII('Onkar'),  
  5.     ASCII(''),  
  6.     ASCII(NULL); 
 

BIT_LENGTH

 
In the BIT_LENGTH(str) function return the String str length in bit forms.
 
Syntax
BIT_LENGTH (str);
 
Example
  1. SELECT BIT_LENGTH('a'),  
  2.     BIT_LENGTH('VATSA'),  
  3.     BIT_LENGTH('Hello!Vatsa'); 
 

CHAR

 
CHAR (N,... [USING charset_name] ) Function returns a string consisting of the character and the given integer value. This function skipped the NULL values.
 
Syntax
CHAR (N, [USING charset_name] );
 
Example
  1. SELECT CHAR(77,121,83,81,'76'); 
 
 

CONCAT

 
The CONCAT(str1, str2?.) function can have one or more arguments and it returns a string that is the result of concatenating the arguments. In this function if all arguments are non-binary strings, then the result is also non-binary string but if any argument is a binary string then the result is a binary string. And a numeric argument is converted to its equivalent binary string form. But if any argument is NULL then it also returns NULL.
 
Syntax
CONCAT (str1, str2...);
 
Example
  1. SELECT CONCAT('In', 'd', 'ia') AS Name,  
  2.     CONCAT('V', 'A', 'T', 'S', 'A') as Name,  
  3.     CONCAT('my', NULL, 'ql') as Name,  
  4.     CONCAT(10, 3) as String; 
 

CONCAT_WS

 
CONCAT_WS () means CONCAT with Separator. The first argument is treated as a separator for the rest of the arguments and it is added between the strings for concatenating. If the separator is NULL then the result is NULL.
 
Syntax
CONCAT_WS (separator str1, str2 ...);
 
Example
  1. SELECT CONCAT_WS(',', 'Title', 'First name', 'Last Name'),  
  2.     CONCAT_WS(',', 'First name', NULL, 'Last Name'); 
 

FIELD

 
The FIELD(str,str1,str2,str3,....) function is used to find the index position of str in the arguments str1,str2,str3. In other words it returns the index position of str in the arguments. It returns 0 if str is not available in the arguments. If str is NULL then return value is 0 because NULL fails equality comparison with any value.
 
Syntax
FIELD (str, str1,str2, str3, ...);
 
Example
  1. SELECT FIELD ('AA', 'BB', 'AA', 'CC'); 
 

FIND_IN_SET

 
FIND_IN_SET (str,strlist) function returns a value in the range of 1 to N. This function finds the String str in the substring of String list strlist” and returns the index value. This String list has many substrings that are separated by “,” Characters.
 
This function returns 0 when “str” is not available in “strlist” or “strlist” is the empty string.
 
Syntax
FIND_IN_SET (str,strlist);
 
Example
  1. SELECT FIND_IN_SET('2', '1,2,3,4'),  
  2.     FIND_IN_SET('7', '14,21,28,35'),  
  3.     FIND_IN_SET('2', ''),  
  4.     FIND_IN_SET('2', '1,2,3,4'); 
 

FORMAT

 
In the FORMAT(X, D) function format, the number X is rounded to the decimal places to D then returns the string as a result. But if D is 0 then, the results don’t have fractional part.
 
Syntax
FORMAT (X, D);
 
Example
  1. SELECT FORMAT(1235.14687, 5),  
  2.     FORMAT(1235.14687, 4),  
  3.     FORMAT(1235.14687, 3),  
  4.     FORMAT(1235.14687, 2),  
  5.     FORMAT(1235.14687, 1); 
 

INSERT

 
This function is used to replace some part or whole String of String “str” with String “newstr” from beginning at position pos and “len” character long. This function returns the String “str” if pos is not within the length of the string. It returns NULL if any argument is NULL.
 
Syntax
INSERT (str, pos,len, newstr);
 
Example
  1. SELECT INSERT('h***oindia', 2, 3, 'ell') AS STRING,  
  2.     INSERT('helloindia', -1, 3, 'net') AS STRING; 
 

INSTR

 
This function is used to return the position of first occurrence of “substr” in “str” String.
 
Syntax
INSTR (str, substr)
 
Example
  1. SELECT INSTR('VATSA', 'A'),  
  2.     INSTR('VATSA', 'O'),  
  3.     INSTR('VATSA', NULL); 
 

LOWER

 
The LOWER (str) function returns the String “str”. And, in this String all the characters are changed in lowercase.
 
Syntax
LOWER (str)
 
Example
  1. SELECT LOWER('HELLO!INDIA...'),  
  2.     LOWER('VATSA'),  
  3.     LOWER('Hello!Vatsa...'); 
 

LEFT

 
This function returns the leftmost “len” characters from the String “str”.
 
Syntax
LEFT (str, len);
 
Example
  1. SELECT LEFT('hello!india', 6),  
  2.     LEFT('Welcome', 3),  
  3.     LEFT('Sunset', 3); 
 

LENGTH

 
The LENGTH (str) function returns the length of the String “str” in bytes.
 
Syntax
LENGTH(str)
 
Example
  1. SELECT LENGTH("HelloIndia"),  
  2.     LENGTH("VATSA"),  
  3.     LENGTH("hello!india..."); 
 

LTRIM

 
LTRIM (str) function is used to return the string “str” with leads in the space characters to be removed.
 
Syntax
LTRIM (str);
 
Example
  1. SELECT LTRIM(' helloindia') AS String,  
  2.     LTRIM('       VATSA            ') AS String,  
  3.     LTRIM('H E L L O ! V A T S A...') AS String; 
 

REPEAT

 
REPEAT (str, count) function returns a string that consist a String “str” repeated of count times. But, if count time is less than 1 than it returns an empty string.
 
Syntax
REPEAT (str,count);
 
Example
  1. SELECT REPEAT('VATSA,...', 11); 
 

REPLACE

 
In REPLACE (str,from_str, to_str) function, it returns the String “str” and in this String all occurrences of the String “from_str” are  replaced by the String “to_str”. This function can perform a case-sensitive match when searching for “from_str”.
 
Syntax
REPLACE (str,from_str, to_str);
 
Example
  1. SELECT REPLACE('www.mcnsolutions.net', 'w', 'W') AS STRING,  
  2.     REPLACE('V@TSA', '@', 'A') AS STRING,  
  3.     REPLACE('Stringg', 'gg', 'g') AS STRING; 
 

RIGHT

 
In the RIGET(str,len) function, it returns the rightmost “len” characters from the String “str”. It returns the NULL if any argument is NULL.
 
Syntax
RIGHT (str, len);
 
Example
  1. SELECT RIGHT('Hello!India', 5) AS STRING,  
  2.     RIGHT('SunFlower', 6) AS STRING,  
  3.     RIGHT('Hello!Miss.', 5) AS STRING; 
 

RTRIM

 
The RTRIM (str) function returns the String “str” with trailing space characters removed.
 
Syntax
RTRIM (str);
 
Example
  1. SELECT RTRIM(' Hello!Vatsa '),  
  2.     RTRIM('Hello! Vatsa     '),  
  3.     RTRIM('       B S R     '); 
 

SPACE

 
The SPACE (N) function returns a String that consists of N space characters.
 
Syntax
SPACE (N);
 
Example
  1. SELECT SPACE(5); 
 

SUBSTRING

 
SUBSTRING(str,pos) and SUBSTRING(str FROM pos) return a substring from str String that is started at position pos. And the other two functions SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len) return a substring, that?s length is len characters, from String str and its started at position pos.
 
Syntax
SUBSTRING (str, pos),
SUBSTRING (str,FROM, pos),
SUBSTRING (str, pos,len),
SUBSTRING (str,FROM pos FOR len);
 
Example
  1. SELECT SUBSTRING('HelloIndia', 5),  
  2.     SUBSTRING('HelloIndia', FROM, 5),  
  3.     SUBSTRING('Hello!Vatsa...', 7, 5); 
 

UPPER

 
UPPER (str) function return the String “str”. And, in this string all the characters are changed in the uppercase.
 
Syntax
UPPER (str);
 
Example
  1. SELECT UPPER('helloIndia'),  
  2.     UPPER('vatsa'),  
  3.     UPPER('Hello! Vatsa...'); 
 

CONCLUSION

 
In this article, I have discussed the concept of MySQL String Functions with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL.
 
Thanks for reading this article!


Similar Articles