In this article, we are going to talk about the MySQL SUBSTRING() function with its variations. Here, you will find a detailed guide with the basic syntax and examples of how to use this function. Moreover, we will demonstrate the benefits of MySQL coding in a handy IDE - dbForge Studio for MySQL that can help you save a lot of time and energy.
Here is a quick and simple way to generate a password your
application using MySQL.
This query will create a upper and lower case randomly generated password in length and values.
SELECT CONCAT(UCASE(SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , FLOOR( 1 + (RAND() * 4 )) , FLOOR( 4 + (RAND() * 12 )) )),
SUBSTRING('!@#$%^&*', FLOOR( 1 + (RAND() * 4)) , FLOOR( 1 + (RAND() * 8)) ),
SUBSTRING( SUBSTRING(MD5(RAND()), FLOOR( 1 + (RAND() * 5)) , FLOOR( 8 + (RAND() * 15 )) ) , 5)) as password \G
This query will take a random value for the 1st part of the dynamic password and upper case it when applicable then the second half it adds some symbols then the remaining is in lower case because MD5 does that automatically. You of course can adjust whatever symbols you would prefer.
So I noticed a few different questions and posts about parsing a
string out of another string recently. While some solutions
included creating new functions and etc it can also be done
within a single query in some cases.
For example, let us say that we are looking to pull out the domain from a URL. I will try to go into detail as to why and how this works.
We have the following table.
CREATE TABLE `parse_example` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`urldemo` varchar(150) NOT NULL,
PRIMARY KEY (`id`)
| id | urldemo |
| 1 | http://www.mysql.com/ |
| 2 | …