Loading

MySQL String Functions Part-1

ASCII Function:- ASCII function give the ASCII value of the first or leftmost character of a given expression.
Syntax:- ASCII(str)
For Example:-
SELECT ASCII(‘Cat’)AS Lower_Case;
output:- 67
SELECT ASCII(‘C’)AS Lower_Case;
output:- 67
CHAR Function:- CHAR function return the character value of each integer argument. Based on the ASCII table. It ignores NULL.
Syntax:- CHAR (N…[USING character set name])
SELECT CHAR(77,121,83,81,’76’);
output:- 0x4D7953514C
SELECT CHAR(77);
Output:- M
CHAR_LENGTH Function:- CHAR_LENGTH function return the length of the given string;
Syntax:- CHAR_LENGTH(str)
For Example:-
SELECT CHAR_LENGTH(“MySQL learning”) ;
output:-14
CHARACTER_LENGTH Function:- This function is the same as the CHAR_LENGTH function. That means the CHARACTER_LENGTH function is synonymous with CHAR_LENGTH. Both function return length of the string.
COERCIBILITY Function:- MySQL sometimes needs to choose which collation to use when the results of an SQL statement involve different types of data. The COERCIBILITY function returns the collation coercibility value of the string argument.
Coercibility Meaning
0 Explicit collation
1 No collation
2 Implicit collation
3 System constant
4 Coercible
5 Ignorable
Lower coercibility levels take precedence over higher ones.
Syntax:- COERCIBILITY(string)
For Example:-
SELECT COERCIBILITY(‘kshtutor’)
output:- 4
SELECT COERCIBILITY(user())
output:- 3
COLLATION Function:- COLLATION function return a collection of a specific string. In the case of a binary argument, it returns NULL.
Syntax:- COLLATION(str)
For Example:- SELECT COLLATION(‘kshtutor’);
output:- utf8_general_ci
SELECT COLLATION(456);
output:- binary
SELECT COLLATION(‘456’);
output:- utf8_general_ci
SELECT COLLATION(CONVERT(‘kshtutor’ USING latin1));
output-latin1_swedish_ci
COMPRESS Function:- COMPRESS Function is used to compress the string. It returns a binary string. COMPRESS() function does not offer the possibility to specify the compression level.
Syntax: COMPRESS(str)
For Example-
SELECT COMPRESS(‘test’),LENGTH(COMPRESS(‘test’));
Output:-
COMPRESS(‘test’) LENGTH(COMPRESS(‘test’)
0x04000000789c2b492d2e0100045d01c1 16
SELECT COMPRESS(‘kshtutor’);
Output:- 0x08000000789ccb2ece28292dc92f02000fb90385
The UNCOMPRESS() function is used in MySQL for returns the original string after uncompressing a compressed string.
For Example:-
SELECT UNCOMPRESS(COMPRESS(‘kshtutor’));
Output:- kshtutor

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w