I am in need of a query that do something like this
UPDATE myTableName SET status = 0 WHERE type ( type contains last character = x)
E.g type values can be
ABCD9238
ADA323S
It needs to detect the second value.
-
Not clear from the given example what last char you will search ?\Abhik Chakraborty– Abhik Chakraborty2015-03-23 09:32:16 +00:00Commented Mar 23, 2015 at 9:32
-
@AbhikChakraborty the last character S let's sayMuhammad Umar– Muhammad Umar2015-03-23 09:34:32 +00:00Commented Mar 23, 2015 at 9:34
Add a comment
|
3 Answers
You can use the right function for this as
mysql> select right('ABCD9238', 1);
+----------------------+
| right('ABCD9238', 1) |
+----------------------+
| 8 |
+----------------------+
1 row in set (0.00 sec)
mysql> select right('ADA323S', 1);
+---------------------+
| right('ADA323S', 1) |
+---------------------+
| S |
+---------------------+
1 row in set (0.00 sec)
So if you are looking for last character to be S then you can use as
UPDATE myTableName SET status = 0 WHERE right(type,1) = 'S'
Comments
You could use Substring function
http://www.w3resource.com/mysql/string-functions/mysql-substring-function.php
Second character from the end
UPDATE myTableName SET status = 0 WHERE substring(type,-2,1) = x
The last character
UPDATE myTableName SET status = 0 WHERE substring(type,-1) = x
Comments
If I understand your requirements, you need to know if a character is in a column "type".
If so you can use LIKE statement.
UPDATE myTableName SET status = 0 WHERE type LIKE "%CHARACTER_YOU_WANT_TO_CHECK%"
If the character is in the last position you should remove the last "%"
It can be used for strings, not only for char's.
Comments
