4.

LENGTH(string) - returns the total number (integer value) of characters in the

specified argument string.

Also counts blank spaces.

This function will return NULL as value if the specified argument value (in case of fields of
a table) is NULL.

mysql> SELECT LENGTH('RESURRECTION’);

+------------------------------------------------------------+
| LENGTH('RESURRECTION’)
+------------------------------------------------------------+
| 12 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Will count and return the total number of characters in the given string (within quotes)
mysql> SELECT LENGTH('RESURRECTION OF OSIRIS’);
+------------------------------------------------------------+
| LENGTH('RESURRECTION OF OSIRIS’)
+------------------------------------------------------------+
| 22 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Will also count the blank spaces in between the first and last characters of the given
string.

mysql> SELECT LENGTH(' RESURRECTION OF OSIRIS’);

+------------------------------------------------------------+
| LENGTH(' RESURRECTION OF OSIRIS’)
+------------------------------------------------------------+
| 23 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH('RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| LENGTH('RESURRECTION OF OSIRIS ’)
+------------------------------------------------------------+
| 24 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(' RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| LENGTH(' RESURRECTION OF OSIRIS ’)
+------------------------------------------------------------+
| 28 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
Recall the table/relation named – SALES and its Field named - PaymentMode

| SELECT PaymentMode FROM SALES; |

+-----------------------------------------------------------+
| CREDIT CARD |
| ONLINE |
| CHEQUE |
| BANK FINANCE |
| CREDIT CARD |
| BANK FINANCE |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

Write the statement to show the total number of characters of the field PaymentMode of
the table named SALES -

mysql> SELECT LENGTH(PaymentMode) FROM SALES;

+-----------------------------------------------------------+
| LENGTH(PaymentMode) |
+-----------------------------------------------------------+
| 11 |
| 6 |
| 6 |
| 12 |
| 11 |
| 12 |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

If the value of the PaymentMode of third record has been changed to NULL. Then what
will be the output for the same above statement ???

| SELECT PaymentMode FROM SALES; |

+-----------------------------------------------------------+
| CREDIT CARD |
| ONLINE |
| NULL |
| BANK FINANCE |
| CREDIT CARD |
| BANK FINANCE |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

The output for a record with a NULL value for a Field will be always – NULL and not the
total characters in NULL (which is 4)

mysql> SELECT LENGTH(PaymentMode) FROM SALES;

+-----------------------------------------------------------+
| LENGTH(PaymentMode) |
+-----------------------------------------------------------+
| 11 |
| 6 |
| NULL |
| 12 |
| 11 |
| 12 |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)
Only when we try to pass NULL as a string value (within quotes) as argument value of
LENGTH( ) then the output will be 4.

mysql> SELECT LENGTH(‘NULL’);

+------------------------------------------------------------+
| LENGTH(‘NULL’)
+------------------------------------------------------------+
| 4 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

An independent statement with value as NULL as argument will return Error / No output.

mysql> SELECT LENGTH(NULL);

+------------------------------------------------------------+
| LENGTH(NULL)
+------------------------------------------------------------+
| NULL |
+------------------------------------------------------------+
0 row in set (0.00 sec)

5. LEFT(string, N) – returns the left most characters(starting from index address 1) from
the specified ‘string’ of total ‘N’ numbers / length of characters.

Will return NULL value if the specified String value is NULL.

Ignores the leading blank spaces.

mysql> SELECT LEFT('RESURRECTION OF OSIRIS’,4);

+------------------------------------------------------------+
| LEFT('RESURRECTION OF OSIRIS’,4) |
+------------------------------------------------------------+
| RESU |
+------------------------------------------------------------+
1 row in set (0.00 sec)

This function will extract first 4 characters from the first index address. (printable
character)

1 2 3 4 5 6 7 8 9 10 11 12
R E S U R R E C T I O N

mysql> SELECT LEFT(‘ RESURRECTION OF OSIRIS’,4);

+------------------------------------------------------------+
| LEFT(‘ RESURRECTION OF OSIRIS’,4) |
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example in the output, leading blank spaces have been ignored and the
counting begins from the first printable character which is ‘R’.

mysql> SELECT LEFT(‘ RESURRECTION OF OSIRIS’,16);

+------------------------------------------------------------+
| LEFT(‘ RESURRECTION OF OSIRIS’,16) |
+------------------------------------------------------------+
| RESURRECTION |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LEFT(‘RESURRECTION OF OSIRIS’ );

+------------------------------------------------------------+
| LEFT(‘RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| Error: |
+------------------------------------------------------------+

Will return no value / error as the second argument has not been specified

| SELECT LEFT(PaymentMode,7) FROM SALES; |

+-----------------------------------------------------------+
| CREDIT |
| ONLINE |
| NULL |
| BANK FI |
| CREDIT |
| BANK FI |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

In the above example the function has returned the 7 left most characters (including
blank spaces if any) of each respective value of the field named PaymentMode from the
table SALES.

6. RIGHT(string, N) - returns the right most characters(starting from the last index
address -1) from the specified ‘string’ of total ‘N’ numbers / length of characters.

Will return NULL value if the specified String value is NULL.

Ignores the blank spaces.

SELECT RIGHT( 'RESURRECTION',4);

This function will extract last 4 characters from the last index address.

-12 -11 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1

R E S U R R E C T I O N

mysql> SELECT RIGHT('RESURRECTION’,4);

+------------------------------------------------------------+
| RIGHT(‘RESURRECTION’,4) |
+------------------------------------------------------------+
| TION |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT RIGHT(‘RESURRECTION OF OSIRIS ’,4);

+------------------------------------------------------------+
| RIGHT('RESURRECTION OF OSIRIS ’,4) |
+------------------------------------------------------------+
| |
+------------------------------------------------------------+
1 row in set (0.00 sec)

-22 -21 -20 -19 -18 -17 -16 -15 -14 -13 -12 -11 -10 -9 -8 -7 -6 -
5 -4 -3 -2 -1
R E S U R E C T I O N \b O F \b O S I R I S

In the above example in the output, trailing blank spaces have been ignored and the
counting began from the right most first printable character which is ‘S’ and total 4
characters have been extracted.

mysql> SELECT RIGHT(‘RESURRECTION OF OSIRIS’ );

+------------------------------------------------------------+
| RIGHT(‘RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| Error: |
+------------------------------------------------------------+

Will return no value / error as the second argument has not been specified

| mysql> SELECT LEFT(PaymentMode,7) FROM SALES; |

+-----------------------------------------------------------+
| CREDIT |
| ONLINE |
| NULL |
| BANK FI |
| CREDIT |
| BANK FI |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

7. INSTR(mainstring, substring) – returns the position number (integer value) of the

first occurrence of the specified substring in the mainstring. Provided the entire
substring should be a perfect match in the main string in the same occurrence order.

mysql> SELECT INSTR('ARMAGEDDON', 'DON' );

+---------------------------------------------------------+
| INSTR('ARMAGEDDON', 'DON' ) |
+---------------------------------------------------------+
| 8 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10

A R M A G E D D O N

In the above example ‘DON’ is a substring (perfect match) in the main string
'ARMAGEDDON' .
So, the function will return the index address of the first character of the substring which
is 8.

mysql> SELECT INSTR('ARMAGEDDON', 'DONN' );

+---------------------------------------------------------+
| INSTR('ARMAGEDDON', 'DONN' ) |
+---------------------------------------------------------+
| |
+---------------------------------------------------------+
0 row in set (0.00 sec)

In the above example there is no such substring ‘DONN’ (exact match) in the main string
'ARMAGEDDON' .
So, the function will return Error / No output.

mysql> SELECT INSTR('HAKUNA MATATA', 'TA' );

+---------------------------------------------------------+
| INSTR('HAKUNA MATATA', 'TA' ) |
+---------------------------------------------------------+
| 10 |
+---------------------------------------------------------+
1 row in set (0.00 sec)

1 2 3 4 5 6 7 8 9 10 11 12 13
H A K U N A M A T A T A

In the above example there are two exact matches of the substring ‘TA’ in the main
string but every time the function will return the index address of the first character of
the first matching substring and which will be 10.

8. LTRIM(string) – returns the new string without the leading blank spaces if any
between the opening quote and first printable character of the string.

mysql> SELECT LTRIM(‘ RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| LTRIM(‘ RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example the function LTRIM( ) has ignored / removed all the leading blank
spaces of the string.

mysql> SELECT LTRIM(‘ RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| LTRIM(' RESURRECTION OF OSIRIS ’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example the function LTRIM( ) has ignored / removed all the leading blank
spaces of the string and even the trailing spaces can’t be identified (removed or still
there)

Now, please note that the SQL string functions have in-built structure to ignore/remove
the leading or trailing blank spaces between the quotes and the first printable character
(from either of the sides)

9. RTRIM(string) - returns the new string without the trailing blank spaces if any.

mysql> SELECT RTRIM(‘RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
 | RTRIM('RESURRECTION OF OSIRIS ’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example the trailing spaces have been removed / ignored and then the
output has been shown.

mysql> SELECT RTRIM(‘ RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| RTRIM(' RESURRECTION OF OSIRIS ’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example both the trailing and trailing spaces have been removed although
the function used here is RTRIM( ) but then both the leading and trailing blank spaces
have been removed.

10. TRIM(string) - returns the new string without the leading and trailing blank spaces
if any. And, TRIM( ‘substring’[, FROM] ‘mainstring’) - returns the new string with
mainstring-substring.
SELECT TRIM( ‘substring’, FROM Column_Name) FROM Table_Name; - returns the
new string without the substring from each row of the mentioned column.

mysql> SELECT TRIM(‘ RESURRECTION OF OSIRIS ’);

+------------------------------------------------------------+
| TRIM(' RESURRECTION OF OSIRIS ’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example both the trailing and trailing spaces have been removed by the
function TRIM( ).

Leading Spaces 1 2 3 4 5 6 7 8 9 Trailing Spaces

‘ \b \b \b E N D S \b W E L L \b \b \b ’

STRING
blank space

** According to your NCERT Book – The below two application of TRIM also works.

mysql> SELECT TRIM(‘OF ’, ‘RESURRECTION OF OSIRIS’);

+------------------------------------------------------------+
| TRIM(‘OF ’, ‘RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| RESURRECTION OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example substring ‘OF ‘ is trimmed from the main string ‘RESURRECTION OF
OSIRIS’

mysql> SELECT TRIM(‘OF ’ FROM ‘RESURRECTION OF OSIRIS’);

+------------------------------------------------------------+
| TRIM(‘OF ’, ‘RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| RESURRECTION OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example substring ‘OF ‘ is trimmed from the main string ‘RESURRECTION OF
OSIRIS’ using the FROM keyword within the argument value of TRIM( ).

mysql> SELECT TRIM(‘OT ’ FROM ‘RESURRECTION OF OSIRIS’);

+------------------------------------------------------------+
| TRIM(‘OT ’, ‘RESURRECTION OF OSIRIS’) |
+------------------------------------------------------------+
| RESURRECTION OF OSIRIS |
+------------------------------------------------------------+
1 row in set (0.00 sec)

In the above example substring ‘OT’ does not exist in the main string ‘RESURRECTION OF
OSIRIS’.
So, when we try to remove / trim a non-existing substring from the main string then the
mainstring will be displayed as it is.

Consider the below given column PaymentMode from the table SALES.
| SELECT PaymentMode FROM SALES; |
+-----------------------------------------------------------+
| CREDIT CARD |
| DEBIT CARD |
| NULL |
| BANK FINANCE |
| CREDIT CARD |
| BANK FINANCE |
+-----------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> SELECT TRIM(‘CARD ’FROM PaymentMode) FROM SALES;

+------------------------------------------------------------+
| TRIM(‘CARD’, PaymentMode) FROM SALES; |
+------------------------------------------------------------+
| CREDIT |
| DEBIT |
| NULL |
| BANK FINANCE |
| CREDIT |
| BANK FINANCE |
+------------------------------------------------------------+
6 row in set (0.00 sec)
In the above example each row if has the substring ‘CARD’ is removed/trimmed and then
the remaining string is being displayed. And the rows which do not have the substring
‘CARD’ are displayed as it is.

MID( ‘mainstring’, Startpos, tot_chars) = SUBSTRING(‘mainstring’, start_pos, tot_chars)

SUBSTRING(“MEASURES” , 4, 3) = SUR

MID(“MEASURES” , 3, 5) =ASURE

*************