MySQL String functions |
String length functionLet us say that you have a string value in your database table, this could be something like employee first name, last name or even a text description of what they do. MySQL and most other relational database systems use the function called LENGTH to retrieve the length or number of characters in that string value. For example: SELECT length (‘Makanga is here’);will return the number 15. This is the number of characters in the string, which include spaces within the text. In other words the function LENGTH is used to count the number of characters in a string. Concatenation functionsThese functions are mainly used to join two or more values into a single string. For example, your database table will have your names, first name, middle name and last name in different fields. There are however, cases when you may need to join the three fields to display the full name, to achieve this results you use concatenation functions to merge the fields into one. For example, Let us say you have the first name: Athanasius, middle name: Mukoya and last name : Kambinda and would like to display the full name as “Athanasius Mukoya Kambinda”. Here is how you go about doing it: If you did: select concat (firstname, middlename,lastname) from employee;you will get AthanasiusMukoyaKambindaThe string did merge all the fields, but something does not look right, all names are squeezed together to an extent that it is almost unreadable. To make your data readable you use what is called concatenating with separator. In this case you will specify what character toy want to use to separate the value. In our case all we want to do is separate the names with single spaces. To do that we use the following command: SELECT concat_ws (‘ ’,firstname, middlename, lastname) from employee;note that within the concat_ws function, we specify the separator, which in this case is a blank space followed by the values we wish to separate. This will produce the following results: Athanasius Mukoya Kambindathe exact format that we wanted!. Trimming and PaddingWhat if my values have spaces or characters at either the beginning or the end that I do not wish to display or use? Well, MySQL or any relational database system for that matter; has functions to make that job easier for you too. You could use the function called LTRIM to remove a number of spaces or characters to the left or the string. If you wanted to get rid of the trailing spaces or characters, you use RTRIM. In some cases if you know the value or characters that need to be trimmed you may use the function TRIM. Usage: SELECT rtrim (‘Muhona ‘); /* returns Muhona with no spaces to the right */and SELECT ltrim (‘ Muhona’); /* get rid of all the spaces to the left */ What if you had some thing like this: Customer number : 0000056645and you really do not care about the leading zeros, all you want to work with are the numbers that follow those zeros. Well, you could use the TRIM function to get rid of those zeros as follows: SELECT trim ( LEADING ‘0’ from ‘0000056645’); If the zeros where at the end of a string as in Mukosho00000, you would use the following statement to trim the zeros off: SELECT trim ( TRAILING ‘0’ from ‘Mukosho00000’); to get Mukosho. Note: If you were using the field names, you do not have to include the names in single quotes, you only use single quotes when you are dealing with the actual value not a field name. The exact opposites of RTRIM and LTRIM are RPAD and LPAD. To use these functions you would use the following syntax: SELECT rpad (‘Mushinga’, 12, ‘X’); // producing MushingaXXXX or What the first statement says is Take the value Mushinga, Ad X to the right until you hav 12 characters. The second statement says, take the value Mushinga, add Y to the lect of the word until you have 12 characters. Substring and string modification functionsSubstring sunction is used to return only part of the whole string. For example if I only wanted to return the first three letters of the string “RENATE”, I would issue the following statement: SELECT substring(‘Renate’,1, 3); Here I am telling the server to return the first 3 letters of the string Renate, Starting at character position 1. However, if you only wanted to return the 3 left-most characters or the last 3 characters you could use the LEFT or RIGHT functions to do that: SELECT left (‘RENATE’,3); // returns REN To change the type case of the string value you can either use UCASE ( M ) to turn the value (M) into upper case or you can use LCASE ( M ) to convert the value (M) to lower case. Other string manipulation functions include REPEAT (“stringvalue”,4) to repeat the value “stringvalue” 4 times and REPLACE (‘stringvalue’, original characters, new characters) in which case, the system will replace the original characters with the new characters in a given string value. |
RELATED ARTICLES:
PHP String functions
So far we have only displayed strings directly to the browser without changing the format. We have successfully done that using the PHP echo () command. In addition to the echo () command, PHP also has two other print functions that allow users to format the output. The formatting may come in the fo
php string strpos
php string str_replace
php string substr_replace
php string strtoupper strtolower
php string implode
Functions Notation
Array related PHP Functions
The PHP language has a lot of functions that are used to populate, retrieve and manipulate array variables. Bellow are some of those functions, we will be using some of them as we continue to learn. Functi
Functions in PHP
Just like we have learned in mathematics, the basic way to think of and understand functions is to think of it as a machine, you feed it with raw materials and the machine goes through processing these materials into finished and possibly usable product. An easy way to look at functions is to consid
PHP Flow control functions