String Functions in mysql

String Functions

The ASCII ( ) function allows you to identify the numeric value of the first character in a string.

SELECT ASCII(‘book’);

The SELECT statement returns the numeric value for the first character, which is the letter b. The numeric  value for the letter b is 98.

(  The ORD() function works just like the ASCII() function except that it also supports multibyte characters. )

CHAR_LENGTH ( ), CHARACTER_LENGTH ( ), and LENGTH ( ) Functions

SELECT CHAR_LENGTH(‘cats and dogs’);

The statement returns a value of 13, which is the number of characters in the string, including spaces.
If you use the LENGTH() function with single-byte characters, the results are the same as with the
CHAR_LENGTH() function, as shown in the following example:

SELECT LENGTH(‘cats and dogs’);

In this case, the result is once again 13. If this were a double-byte character string, though, the result
would be 26 because the LENGTH() function measures in bytes, not characters.

concatenates five values:

SELECT CONCAT(‘cats’, ‘ ‘, ‘and’, ‘ ‘, ‘dogs’);

concatenates with space:

CONCAT_WS(<separator>, <string1>, <string2> [{, <string>}...])

By using this function, the separator is automatically inserted between the values. If one of the values is  NULL, the separator is not used. Except for the separator, the CONCAT_WS() function is the same as the  CONCAT() function. For example, the following SELECT statement concatenates the same words as in the  last example:

SELECT CONCAT_WS(‘ ‘, ‘cats’, ‘and’, ‘dogs’);

Notice that the CONCAT_WS() function identifies the separator (a space) in the first argument and that  the separator is followed by the string values to be concatenated. The output from this function (cats and  dogs) is the same as the output you saw in the CONCAT() example.

INSTR() function returns the position of dogs in the string:

SELECT INSTR(‘cats and dogs’, ‘dogs’);
the substring dogs begins in the tenth position, so the function returns a value of 10


LOCATE(<substring>, <string>)
The syntax for the LOCATE() and INSTR() functions is similar except that, with
LOCATE(), the substring is listed first, as shown in the following example:

SELECT LOCATE(‘dogs’, ‘cats and dogs’);

LOCATE(<substring>, <string>, <position>)
The function includes a third argument, <position>, which identifies a starting position in the function. This is the position at which the function should start looking for the substring. For example, suppose that you create the following SELECT statement:

SELECT LOCATE(‘dogs’, ‘cats and dogs and more dogs’, 15);

Notice that the LOCATE() function includes a third argument: 15. This is the position at which the function should begin looking for the substring dogs. As a result, the function disregards the first occurrence of dogs because it is before position 15 and returns a value of 24, which is where the second dogs begins.

LCASE(), LOWER(), UCASE(), and UPPER() Functions

change string values to upper or lowercase

SELECT LOWER(‘Cats and Dogs’);
SELECT UPPER(‘cats and dogs’);

LEFT() and RIGHT() Functions

functions that return only a part of a string value. For example, you can use the LEFT() function to return only a specific number of characters from a value
LEFT(<string>, <length>)

The <length> value determines how many characters are returned, 

starting at the left end of the string.

SELECT LEFT(‘cats and dogs’, 4);
Because the value 4 is specified in the function arguments, the function returns the value cats.

SELECT RIGHT(‘cats and dogs’, 4);
In this case, the statement returns the value dogs.

suppose that you want to create a user ID for your employees based on their first and last names. 

You can use the LEFT() function to take the first three letters of their first names and the
first four letters of their last names and then use the CONCAT() function to join these extracted values together.

REPEAT() and REVERSE() Functions

The REPEAT() function, shown in the following syntax, is used to repeat a string a specific number of times:

SELECT REPEAT(‘CatsDogs’, 3);
The result from this function is CatsDogsCatsDogsCatsDogs.

SELECT REVERSE(‘dog’);
The value returned by this function is god

SELECT SUBSTRING(‘cats and dogs’, 10);
you must specify the string and the starting position. The function then returns a substring that includes the rest of the string value, starting at the identified position.

SELECT SUBSTRING(‘cats and dogs and more dogs’, 10, 4);
This form includes the <length> argument, which allows you to specify how long (in characters)

 the  substring should be.

SELECT DVDName, CHAR_LENGTH(DVDName) AS CharLength
FROM DVDs
WHERE CHAR_LENGTH(DVDName)>10
ORDER BY DVDName;

--------------------------------------------------------------------------------------------------------
SELECT EmpID, CONCAT_WS(‘ ‘, EmpFN, EmpMN, EmpLN) AS Name
FROM Employees
ORDER BY EmpLN;

----------------------------------------------------------------------------------------------------------

SELECT EmpID, CONCAT(LEFT(EmpFN, 2), LEFT(EmpLN, 3), EmpID) AS RegID
FROM Employees
ORDER BY EmpID;

------------------------------------------------------------------------------------------------------------------

SELECT EmpID, UPPER(CONCAT_WS(‘ ‘, EmpFN, EmpMN, EmpLN)) AS Name,
CONCAT(LOWER(LEFT(EmpFN, 2)), LOWER(LEFT(EmpLN, 3)), EmpID) AS RegID
FROM Employees
ORDER BY EmpID;