Removing parts of data in a column

[howtowork]

hi everyone,

I am fairly new so easy things trip me up, I have data in a column that is entered as 'BE-333' or 'REW-478' or 'BD222' or 'BD-44RD'. which of course shouldn't have been entered that way. My problem is how to go about seperating the fisrt set of characters from the numbers (less the dash and toss the ending chars) and then put them in separate fields. Is a procedure with if statements the best way to start or a substr?? I have just started my research on this so any help is well...helpful.

Thanks.

 

[carp]

To get the first part,

SELECT substr(the_col,1,INSTR(the_col,'-',1)-1)
FROM my_table;

To get from the dash on to the end:

SELECT substr(the_col,INSTR(the_col,'-',1), length(the_col))
FROM my_table;

Now, to take the value from one column and stick it into two separate columns:

UPDATE my_table
SET
prefix_column = substr(the_col,1,INSTR(the_col,'-',1)-1),
suffix_column = substr(the_col,INSTR(the_col,'-',1), length(the_col));

The only thing left to figure out is how you want to handle values that don't have a dash in them. These values will wind up in the suffix column and the prefix column will be null.