Find single value and replace with dynamic value in table

[DrunkMunki]

i have no idea where to start but;
i'm looking for a way to change all emails from [email][e-mail][/email] to [email][e-mail][/email] in a MySQL DB.

DB Name: simons_directory
Table: listing
Fields: title, email

caveats - title has words with spaces, if possible to remove the spaces so "My Business Name" would equal "MyBusinessName"

any ideas?

can be in a MySQL statement or php code.

[urtrivedi]

YOU need to run update query in mysql
to update emails

update listing set email='[e-mail]' where 
email='[e-mail]'

Be carefull before using following query. it will set title to title without spaces in all records, so better you add new column say title_wo_space, then use following query

update listing set title_wo_space=replace(title,' ','')

[DrunkMunki]

YOU need to run update query in mysql to update emails

update listing set email='[e-mail]' where 
email='[e-mail]'

Be carefull before using following query. it will set title to title without spaces in all records, so better you add new column say title_wo_space, then use following query

update listing set title_wo_space=replace(title,' ','')

thank you for your very quick reply, but i want to change it to $[e-mail] where $title reflects the values in the 'title' field, making sure the spaces are removed from the $title email.

so for example:
title: Bobs Cleaning
email: [email][e-mail][/email] (changed from [email][e-mail][/email]

[urtrivedi]

UPDATE listing SET email=SELECT CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) )

[smantscheff]

For more complicated pattern match and replacement you could also dump the database to a file, run it through sed and import it again.

[urtrivedi]

run it through sed

Hi smantscheff, I do not understand this, please explain.

[DrunkMunki]

Hi smantscheff, I do not understand this, please explain.

i think he means u export the SQL file and use sed and use it to make the changes then re-import the sql file.

[DrunkMunki]

UPDATE listing SET email=SELECT CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) )

i dont think that will do it, keep in mind it dont want to alter any other accounts just ones with supplier@. i think i might have to do it the SED way with regex.

[urtrivedi]

you can use where condition to filter records

UPDATE listing SET email=SELECT CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) ) where email like 'supplier@%'

[smantscheff]

If you are not comfortable with sed, try EditPad Pro which has a very comfortable search and replace interface for RegEx's. It's PREG style, so that you can even use the ungreedy modifier, which can make operations quite simple. Just export the database to a textfile, load it in EditPad, do your replacements and re-import it.

[DrunkMunki]

you can use where condition to filter records

UPDATE listing SET email=SELECT CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) ) where email like 'supplier@%'

i tried your example and get the error below

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email, '@' ) ) ) WHE' at line 1

[DrunkMunki]

If you are not comfortable with sed, try EditPad Pro which has a very comfortable search and replace interface for RegEx's. It's PREG style, so that you can even use the ungreedy modifier, which can make operations quite simple. Just export the database to a textfile, load it in EditPad, do your replacements and re-import it.

I have PowerGREP for windows, but i dont see how regex can find an email and replace it with a seperate variable for every line.
REGEX has always been a pitfall for me.

[DrunkMunki]

im thinking this is more of a PHP fix.
anyone here know php?

[smantscheff]

That cannot be that much of a problem. Show some actual testdata.

[DrunkMunki]

That cannot be that much of a problem. Show some actual testdata.

as requested here are the first 5 lines.

INSERT INTO `Listing` (`id`, `account_id`, `image_id`, `thumb_id`, `promotion_id`, `country_id`, `state_id`, `region_id`, `city_id`, `area_id`, `updated`, `entered`, `renewal_date`, `discount_id`, `title`, `seo_title`, `friendly_url`, `email`, `show_email`, `url`, `display_url`, `address`, `address2`, `zip_code`, `zip5`, `latitude`, `longitude`, `maptuning`, `phone`, `fax`, `description`, `description1`, `description2`, `description3`, `description4`, `seo_description`, `seo_description1`, `seo_description2`, `seo_description3`, `seo_description4`, `long_description`, `long_description1`, `long_description2`, `long_description3`, `long_description4`, `keywords`, `keywords1`, `keywords2`, `keywords3`, `keywords4`, `seo_keywords`, `seo_keywords1`, `seo_keywords2`, `seo_keywords3`, `seo_keywords4`, `attachment_file`, `attachment_caption`, `status`, `level`, `random_number`, `reminder`, `fulltextsearch_keyword`, `fulltextsearch_where`, `video_snippet`, `importID`, `hours_work`, `locations`, `claim_disable`, `cat_1_id`, `parcat_1_level1_id`, `parcat_1_level2_id`, `parcat_1_level3_id`, `parcat_1_level4_id`, `cat_2_id`, `parcat_2_level1_id`, `parcat_2_level2_id`, `parcat_2_level3_id`, `parcat_2_level4_id`, `cat_3_id`, `parcat_3_level1_id`, `parcat_3_level2_id`, `parcat_3_level3_id`, `parcat_3_level4_id`, `cat_4_id`, `parcat_4_level1_id`, `parcat_4_level2_id`, `parcat_4_level3_id`, `parcat_4_level4_id`, `cat_5_id`, `parcat_5_level1_id`, `parcat_5_level2_id`, `parcat_5_level3_id`, `parcat_5_level4_id`, `listingtemplate_id`, `custom_checkbox0`, `custom_checkbox1`, `custom_checkbox2`, `custom_checkbox3`, `custom_checkbox4`, `custom_checkbox5`, `custom_checkbox6`, `custom_checkbox7`, `custom_checkbox8`, `custom_checkbox9`, `custom_dropdown0`, `custom_dropdown1`, `custom_dropdown2`, `custom_dropdown3`, `custom_dropdown4`, `custom_dropdown5`, `custom_dropdown6`, `custom_dropdown7`, `custom_dropdown8`, `custom_dropdown9`, `custom_text0`, `custom_text1`, `custom_text2`, `custom_text3`, `custom_text4`, `custom_text5`, `custom_text6`, `custom_text7`, `custom_text8`, `custom_text9`, `custom_short_desc0`, `custom_short_desc1`, `custom_short_desc2`, `custom_short_desc3`, `custom_short_desc4`, `custom_short_desc5`, `custom_short_desc6`, `custom_short_desc7`, `custom_short_desc8`, `custom_short_desc9`, `custom_long_desc0`, `custom_long_desc1`, `custom_long_desc2`, `custom_long_desc3`, `custom_long_desc4`, `custom_long_desc5`, `custom_long_desc6`, `custom_long_desc7`, `custom_long_desc8`, `custom_long_desc9`) VALUES
(125609, 388, 453, 454, 0, 1, 0, 0, 0, 0, '2010-05-24 03:49:03', '2010-05-14 11:51:25', '2011-12-31', '', '101 Racing', '101 Racing ', '101-racing--4bf9f72f4f07e', '[e-mail]', 'y', 'www.101racing.com', '', '', '', '', '0', 0.000000, 0.000000, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 70, 945229141921950, 0, '101 Racing  Performance Performance brakes & clutch Performance Turbo & Cooling Performance Transmission Performance enginess', 'Australia', '', 53, '', '', 'n', 17, 0, 0, 0, 0, 92, 17, 0, 0, 0, 19, 17, 0, 0, 0, 20, 17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(125610, 389, 0, 0, 0, 1, 4, 0, 0, 0, '2010-05-24 03:49:03', '2010-05-14 11:51:25', '2011-12-31', '', '2FreakUOut Customs', '2FreakUOut Customs', '2freakuout-customs-4bf9f72f78856', '[e-mail]', 'y', '', '', '', '', '', '0', 0.000000, 0.000000, '', '', '', 'Established in 2003 2FREAKUOUT Customs has grown to become one of the finest and most respected smash repair business in Sydney Australia. Continually Reinvesting in the latest paint technology and equipment to keep up with the advances in the industry, ', '', '', '', '', '', '', '', '', '', 'Established in 2003 2FREAKUOUT Customs has grown to become one of the finest and most respected smash repair business in Sydney Australia. Continually Reinvesting in the latest paint technology and equipment to keep up with the advances in the industry, 2FREAKUOUT conducts work for the some of the largest insurance companies including NRMA, Allianz, GIO, Suncorp and QBE. We understand that your vehicle is much more than just another car- it is a valuable investment which requiresthe utmost care to keep it in optimum condition. To repair your damaged vehicle is simply not enough. Each vehicle is restored to it''s original factory specifications and standard. By incorporating only the best technology available in motor vehicle repair, we''ll have your car back on the road looking as good as the day you bought it. On the other hand if you want your car to stand out from the crowd 2FREAKUOUT has the capability and experience to turn your ride from mild to wild!', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 70, 959686059467202, 0, '2FreakUOut Customs Exterior Exterior Body kits & Styling Exterior Paint & Graphics Exterior Other Exterior Established in 2003 2FREAKUOUT Customs has grown to become one of the finest and most respected smas', 'Australia NSW', '', 53, '', '', 'n', 4, 0, 0, 0, 0, 77, 4, 0, 0, 0, 14, 4, 0, 0, 0, 16, 4, 0, 0, 0, 0, 0, 0, 0, 0, 0, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(125611, 390, 449, 450, 0, 1, 0, 0, 0, 0, '2010-05-24 03:49:03', '2010-05-14 11:51:25', '2011-12-31', '', '5zigen Team', '5zigen Team ', '5zigen-team--4bf9f72f7d674', '[e-mail]', 'y', 'www.5zigenaustralia.com', '', '', '', '', '0', 0.000000, 0.000000, '', '072-995-8005', '', '', '', '', '', '', '', '', '', '', '', 'A Message from CEO: Masaharu Kinoshita\n\nThere are countless needs to fill in each market while in the meantime, time is always changing. There is a need to evaluate each market with care to offer superior products that is hungered by the end-users. Thus, advancements in technology is always needed.\n\nFor this reason, we 5ZIGEN center our product research and development from motorsports. By examining an utilizing data from motorization, we can state with confidence our ability to produce our level of manufacture.\n\nData from the racing circuit is reflected in each item we carry for use on the street. We want to offer you a new dimension of driving style to you.\n\nI strongly believe that is why we, 5ZIGEN has our place here.\n', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 70, 962742902303806, 0, '5zigen Team  Performance Performance Muffler & Exhaust', 'Australia', '', 53, '', '', 'n', 17, 0, 0, 0, 0, 21, 17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''),
(125612, 391, 451, 452, 0, 1, 0, 0, 0, 0, '2010-05-24 03:49:03', '2010-05-14 11:51:25', '2011-12-31', '', '6Boost', '6Boost', '6boost-4bf9f72f7fd86', '[e-mail]', 'y', 'http://www.6boost.com/', '', '', '', '', '0', 0.000000, 0.000000, '', '0406 541 441', '', 'Custom Turbo Exhaust Manifolds and Parts', '', '', '', '', 'Custom Turbo Exhaust Manifolds and Parts', '', '', '', '', 'Custom Turbo Exhaust Manifolds and Parts', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'A', 70, 934656363851071, 0, '6Boost Performance Performance brakes & clutch Performance Turbo & Cooling Performance Transmission Performance enginess Custom Turbo Exhaust Manifolds and Parts', 'Australia', '', 53, '', '', 'n', 17, 0, 0, 0, 0, 92, 17, 0, 0, 0, 19, 17, 0, 0, 0, 20, 17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', 'n', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''),

[smantscheff]

But urtrivedi already gave you the solution for that.
What's wrong with

UPDATE listing 
SET email=concat( 
  replace(title,' ', ''),
  substr( email, locate( '@', email ))
);

How do you feed those queries into mysql? The error you've got seems to indicate that you are using the wrong tools.
Also let me remark that the listings table shows a very poor design.

[urtrivedi]

There was select keyword near = sign, thats why you might have got error, following should work.

UPDATE listing SET email= CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) ) WHERE email LIKE 'supplier@%'

[DrunkMunki]

There was select keyword near = sign, thats why you might have got error, following should work.

UPDATE listing SET email= CONCAT(REPLACE(title, ' ','' ) , SUBSTR(email, INSTR(email,  '@' ) ) ) WHERE email LIKE 'supplier@%'

ok with the new code i ran it, says below:

0 row(s) affected. ( Query took 1.2118 sec )

and nothing was changed.

I use phpmyadmin to execute the code.
i know the code is bad, its from software we bought and have no choice over the matter.

[urtrivedi]

make supplier plural add 's' before @ sign
'suppliers@%'

[DrunkMunki]

beautiful, works like a charm.
thank you guys for all your help.