Substrings Functions in MySQL
String manipulation & substring extraction are an important part of any programming language, so it to RDBMS systems like MySQL where it helps to off-load processing at program end by processing string as required at the server end, also sometimes it turns double efficient as the database server caches the result.
Older database systems or version lacked string manipulation functions, but newer day RDBM system have in-built functions to trim, change case, convert between charsets, compress/uncompress. In this article we'll look into substring functions in MySQL, I'll stress on the SUBSTRING_INDEX function which is very handy by seldom made use of by programmers.
The SUBSTRING function is the most common function found in all programming languages & database servers. This function accepts 2 mandatory arguments and 1 option argument. The first 2 being the string to be worked upon and the offset (the offset is 1-based, i.e. it starts from 1 not 0) to start from respectively, the third and optional argument being the length to be extracted from the offset. Please follow the queries & their output below:
As you can see in the example a negative offset value set the start of string counting from the end, it's sometimes useful, in a scenario where for example you need the last 5 characters of a string.
This function is similar to the previous function we discussed, in this function the offset is replaced by a delimiter (single or multiple characters), and length is replaced by count - the number of occurrences of the specified delimiter. If the count is a positive value then all characters to left of the specified occurrence of the delimiter is returned and if it's positive all characters to the right is returned. Now, let's understand this with the example below:
As you saw in the example, the delimiter acts as a search string to start with and help us manipulate string in MySQL databases much better. I hope this article will help you make the best use of substring functions in MySQL.
|All times are GMT +5.5. The time now is 13:19.|