0

I have two columns in my mysql table, equipment and orderno, Here equipment number is manually inserted, and they are in the form C1234,C3212 etc.

I want to strip the C from equipment column and insert the remaining number to orderno column. I have seen that mysql substring_index() can effectively get substring but I am not sure how to make it automtically do the changes, when the equipment column changes.

fthiella
  • 48,073
  • 15
  • 90
  • 106
Rohith Raveendran
  • 410
  • 1
  • 6
  • 14

2 Answers2

1

You could use two triggers, one that's fired before an INSERT, and one that's fired before an UPDATE, to automatically update orderno based on equipment column:

CREATE TRIGGER upd_your_table BEFORE UPDATE ON your_table 
FOR EACH ROW
  SET new.orderno=substring(new.equipment, 2)
;
CREATE TRIGGER ins_your_table BEFORE INSERT ON your_table 
FOR EACH ROW
  SET new.orderno=substring(new.equipment, 2)
;

To update existing values, you could use this:

UPDATE your_table SET orderno=substring(equipment, 2)

See this fiddle

fthiella
  • 48,073
  • 15
  • 90
  • 106
1

Try this ::

UPDATE myTable set orderno= REPLACE(equipment, 'C', '') 
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71