MySQL Remove first two characters of all fields
UPDATE d1
SET d1_val = SUBSTRING_INDEX(d1_val , ":",-1);
Does the job also, leaves "N/A" as it is.
More safe query regarding multiple executes is using LIKE '_:%'
:
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val LIKE '_:%'
It will change every line which looks like C:10
, E:100
etc. but not 10
, 100
after one previous query run.
(Assuming there is always one letter before :
. If more - use LIKE '%:%'
)
Try
UPDATE d1
SET d1_val = SUBSTRING(d1_val, 3)
WHERE d1_val <> 'N/A'
use SUBSTRING(column_name, character_to_remove)
Solution,
SUBSTRING(d1_val, 3)
Note: you can use it on SELECT or UPDATE statement