How can I separate one column to two column?

Question

I have this table :

+---------+---------------+------------+  
| c_data  | c_characters  | c_numbers  |  
+---------+---------------+------------+  
| abc123  |               |            |
+---------+---------------+------------+  
| hb045   |               |            |
+---------+---------------+------------+  
| awrg5   |               |            |
+---------+---------------+------------+  
| xyz456  |               |            |
+---------+---------------+------------+

I want separate c_data characters and numbers, then update to c_characters and c_numbers fields
How can I update field with query?

You can use regular expressions in your Query to do this. See https://dev.mysql.com/doc/refman/5.1/en/regexp.html for guidance. — Mattias Lindberg, Oct 08 '15 at 07:50
See this previous post: http://stackoverflow.com/questions/25401849/how-to-split-the-string-in-one-column-into-two-columns — DTH, Oct 08 '15 at 08:00
@DTH That question has a specific delimiter character. How would you do it here, where he just wants to split it into alphabetic and numeric parts? — Barmar, Oct 08 '15 at 08:08
@MattiasLindberg MySQL can only use regexp for testing whether a column matches, it doesn't have a function to return the part of the column that matches. — Barmar, Oct 08 '15 at 08:09

score 2 · Accepted Answer · edited May 23 '17 at 11:58

Unfortunately, MySQL doesn't (yet) have any REGEXP_REPLACE() function like other databases. You can give issue 29781 some love if you want.

But since you're only looking for digits as a separation criteria, you can write the following statement to get the result you're looking for:

UPDATE my_table
SET c_characters =
  replace(
    replace(
      replace(
        replace(
          replace(
            replace(
              replace(
                replace(replace(replace(c_data, '0', ''), '1', ''), '2', ''), '3', ''
              ), '4', ''
            ), '5', ''
          ), '6', ''
        ), '7', ''
      ), '8', ''
    ), '9', ''
  ),
  c_numbers =
    replace(v, 
      replace(
        replace(
          replace(
            replace(
              replace(
                replace(
                  replace(
                    replace(replace(replace(c_data, '0', ''), '1', ''), '2', ''), '3', ''
                  ), '4', ''
                ), '5', ''
              ), '6', ''
            ), '7', ''
          ), '8', ''
        ), '9', ''
      ), ''
    )

thanks, select query worked, now how can i update field? please change this select query to update query — nima, Oct 08 '15 at 08:27

How can I separate one column to two column?

1 Answers1