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.
UPDATE table A SET col1 = CASE WHEN col1 LIKE '%_%' THEN ... ELSE col1;
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) = '_'