Getting last 5 char of string with mysql query
MysqlVarcharMysql Problem Overview
I have to get last 5 numbers using mysql.
My values are like YOT-A78514,LOP-C4521 ...
I have to get only last five char . How can I do this in query?
Mysql Solutions
Solution 1 - Mysql
You can do this with RIGHT(str,len) function. Returns the rightmost len characters from the string str,
Like below:
SELECT RIGHT(columnname,5) as yourvalue FROM tablename
Solution 2 - Mysql
"Right"-function is the way to, using the substring may lead to an problem that is not so easy to notice:
mysql> select right('hello', 6);
+-------------------+
| right('hello', 6) |
+-------------------+
| hello |
+-------------------+
1 row in set (0.00 sec)
mysql> select substring('hello', -6);
+------------------------+
| substring('hello', -6) |
+------------------------+
| |
+------------------------+
1 row in set (0.00 sec)
But if you don't try to go past the start of the string, then substring of course works fine:
mysql> select substring('hello', -5);
+------------------------+
| substring('hello', -5) |
+------------------------+
| hello |
+------------------------+
1 row in set (0.00 sec)
Solution 3 - Mysql
Right is a good choice but you can also use substring like this-
SELECT Substring(columnname,-5) as value FROM table_name
Solution 4 - Mysql
SELECT row_id
FROM column_name
WHERE column_value LIKE '%12345';
This will return the "row_id" when "12345" is found to be the tailing suffix of the "column_value" within the "column_name".
Solution 5 - Mysql
And if you want to get a dinamic number of right characters after a character:
SELECT TRIM(
RIGHT(
database.table.field,
(LENGTH(database.table.field) - LOCATE('-',database.table.field))
)
)
FROM database.table;
Solution 6 - Mysql
SELECT SUBSTR('Stringname', -5) AS Extractstring;