Tuesday, 15 May 2012

MySQL - populating a column with a substring from another column in the same row -


I would like to run on a full table, which will populate the value of a newly created column with another substrung value Will make the column

In view of the table structure, unlike the following table:

  + -------- + ------------ - + ------ + ----- + --------- + ---------------- + | Field | Type | Faucet Key | Default | Extra | + -------- + -------------- + ------ + ----- + --------- + - - ------------- + | ID | Int (11) | No | PRI | Faucet Auto_interpretation | | Email | Varchar (150) | Yes. | Faucet | | Domain | Varchar (100) | Yes. | Faucet | + -------- + -------------- + ------ + ----- + --------- + - - ------------- +   

contains data:

  + ---- + --- - --------------------- + -------- + | ID | Email | Domain | + ---- + ------------------------- + -------- + | 1 | Bob@domain1.com | Faucet | 2 | Jim@domain1.com | Faucet | 3 | Terry@domain1.com | Faucet | 4 | Frank @ anotherdomain.com | Faucet | 5 | Linda@anotherdomain.com | Faucet | 6 | Craig @ thetheird.com | Faucet + ---- + ------------------------- + -------- +   < P> I have a query to parse the domain part of the email address and put it in the Domain column, as it ends with the result:  
  + ---- + - ------------------------ + ------------------- + | ID | Email | Domain | + ---- + ------------------------- + ------------------ - + | 1 | Bob@domain1.com | Domain1.com | | 2 | Jim@domain1.com | Domain1.com | | 3 | Terry@domain1.com | Domain1.com | | 4 | Frank @ anotherdomain.com | Anotherdomain.com | | 5 | Linda@anotherdomain.com | Anotherdomain.com | | 6 | Craig @ thetheird.com | Thithard.com | + ---- + ------------------------- + ------------------ - +   

Currently, I'm doing of MySQL engine with a shell script, but it is disabled, and I'm sure there is one It is a better way to do this inside the MySQL engine.

The efficiency here is important, because the tables I'm doing in production are tens or even hundreds of thousands of rows.

You can use:

  SELECT id, email, SUBSTRING_INDEX (email, '@',   

or to update your data:

  Set your thread to domain = SUBSTRING_INDEX (email, '@ ', -1)   

Please see Bela.

No comments:

Post a Comment