AbsoluteBeginner AbsoluteBeginner -4 years ago 84
MySQL Question

Update when third character is "_"

I want to update column 1 in table A in a specific way: When the third character in the column is an "_" I want to insert the first 2characters, if the third character anythin else I want to leave it as it is.

Example:

|col1|
+--------+
|161_512 |
|16_1217 |
|161_512 |
|161512 |
|17_0117 |
|1615_12 |


Expected outcome:

|col1|
+--------+
|161_512 |
|16 |
|161_512 |
|161512 |
|17 |
|1615_12 |


Thats what I´ve got so far - but this is not working correctly:

UPDATE table A SET col1 = CASE WHEN col1 LIKE '%_%' THEN ... ELSE col1;

Answer Source

If you want to match a literal underscore in your LIKE expression, you will need to escape it using backslash. An unescaped underscore means match any single character. However, I would reword your query such that it uses a WHERE clause to determine whether or not to update a given record.

UPDATE table A
SET col1 = ...
WHERE col1 LIKE '__\_%'

Note carefully here that LIKE __\_% says to match any two characters, followed by a literal underscore, followed by anything else.

If you don't feel comfortable dealing with all this, you can always use a substring to check the value of the third character:

UPDATE table A
SET col1 = ...
WHERE SUBSTRING(col1, 3, 1) = '_'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download