|
Mysql left and right functions |
||||||||
When you need to extract specific elements from a column, MySQL has a few functions that can help. I've always found it much easier to provide the data to your application layer in the corrct format in the first place, rather than excessive processing in GUI layer.
Suppose I needed to check that column in the table always has a trailing '/' on the column data.
1select id,
2 name,
3 RIGHT(url, 1) as slash
4 from table
5 where RIGHT(url, 1) != "/"
In the LEFT() function, using the column telephone along with the number of characters to extract, starting from the first character on the left in the column.
2 name,
3 RIGHT(url, 1) as slash
4 from table
5 where RIGHT(url, 1) != "/"
The RIGHT() function is similar, but it starts from the last character on the right, counting left to encapsulate the last seven characters.
In the SQL statement below, areaCode is reused to order the results set. To reformat the telephone number, it will be necessary to use the SUBSTRING() function.
1SELECT LEFT(telephone, 3) AS areaCode,
2 RIGHT(telephone, 7) AS telNo
3 FROM contacts
4 ORDER BY areaCode;
5
6 SELECT CONCAT('(', LEFT(telephone, 3), ') ',
7 SUBSTRING(telephone, 4, 3), '-',
8 MID(telephone, 7)) AS 'Telephone Number'
9 FROM contacts
10 ORDER BY LEFT(telephone, 3);
2 RIGHT(telephone, 7) AS telNo
3 FROM contacts
4 ORDER BY areaCode;
5
6 SELECT CONCAT('(', LEFT(telephone, 3), ') ',
7 SUBSTRING(telephone, 4, 3), '-',
8 MID(telephone, 7)) AS 'Telephone Number'
9 FROM contacts
10 ORDER BY LEFT(telephone, 3);
TweetBacks
There are no comments for this entry.
[Add Comment] [Subscribe to Comments]