Getting last 5 char of string with mysql query

问题:

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?

回答1:

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

回答2:

"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)

回答3:

Right is a good choice but you can also use substring like this-

SELECT Substring(columnname,-5) as value FROM table_name

回答4:

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;

回答5:

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".