MYSQL :: Search & Replace In Database?

Search & Replace?
Newbie here.

I have a DB with two tables.

Table 1 has three fields - a unique ID#, a first name (i.e - John), and a last name (i.e. - Smith).

Table 2 had many fields, one of them being a first name/last name combination (i.e. John Smith).

I want to replace the first/last combo field in table 2 with the unique ID# from table 1 which matches the concat of the first name field and the last name field from table 1.

I know I can do it by hand, but there are thousands of them in table 2 that need to be replaced and the data in table 2 is in the format that I will be receiving from an outside source, so I will need to search & replace this data on a regular basis.

Search And Replace
I want to replace a string inside a text field named "text".

Here is a query which would show the records which contain the offending code which I want to strip out:

SELECT * FROM articlestable where text like Concat('%', '<b class=titler>', '%', '</b>', '%')

Search And Replace
Have a website where we have just changed domain names, but am having a problem because content has been submitted that occasionally uses absolute URL's which has resulted in lots of links through the site pointing to the old domain.
What I want to be able to do is do a search and replace in MySQL that replaces all use of the old domain name with the new one. I've found several search and replace guides around the internet, but they all seem to do a search and replace on a specific field on a specific table like this on;
Code:

UPDATE tablename SET tablefield = replace(tablefield,"findstring","replacestring");

Simple Search And Replace
How do I search for all the occurrences of a given string in my whole database and replace them with another string?

Search And Replace In MYSQL
I want to search through some text and find where width is bigger than 200 and replace it with 300. I'm using MySQL 4.1.11. This is what i got so far.

update `Artikkel`
set `Hoved` = replace(`Hoved`, regexp 'width="(.*)"', 'width="300"')
where convert(int, substring(`Hoved`, instr(`Hoved`, 'width') + 7, 3)) > 200;

But i get this error:
#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 'regexp 'width="(.*)"', 'width="300"')
where convert(int, substring(`Hoved`, i' at line 2

Text Field Search/replace
I need to do a mass search & replace on a TEXT column in a db. All I need to do is replace a few characters, but there's over 500 affected rows. Is there a way to do this in MySQL? I've created some html forms to do the work manually, if I have to.

Search And Replace On Char Field
I am trying to do a search and replace on a char(11) field. My guess is that the following SQL will replace all occurences of the string:

UPDATE table_name SET table_field=REPLACE(table_field,'string_to_find','string_to_replace');

However, I only want to replace the string if it occurs at the beginning of the field. I would guess I need to use LIKE 'string_to_find%' somehow, but I'm not sure on how to combine this with the above statement.

Search/replace Ignore Case
I'm using the following to search a field and replace some text:

update `dbName`.`tableName` set fldName=
replace(lower(`dbName`.`tableName`.`fldName`),lower('OldTEXT'),'NEWtext');

Problem is, if it doesn't find the text, it turns the entire row lower case.

So, I wrote it this way:

update `dbName`.`tableName` set fldName=
replace(`dbName`.`tableName`.`fldName`,lower('OldTEXT'),'NEWtext');

Then it doesn't work at all.

Search: Remove Quotes From Database For Search
I am making a book site and currently in the database are book titles likes "John's Trip" and "Peter's new bike". I have a search function where the customer can type in "Peter's" and it will bring up everything with "Peter's" in it.

But i was wonderind is there any way i can search for "Peters" and "Johns".

Can I Replace The Files MYI MYD And FRM To Update A Database?
Can i update a MySQL database with just copy the files MYI MYD and FRM
in the right dir? I know this work. The question is how MySQL manage
the user that are usig that database in that moment. Can i close all
the connection to that database? How can i tell MySQL deamon that
don´t use that database any more?

How To Search '+','-' Or '(' In Database
How to search " +, - , ( " signs in mysql database....

Database Search
how to setup a search engine for my database. Right now I have 4 different tables, all relating to each other thru different keys. I currently use a search for where you must use drop downs to fill in text fields. I want to change over to a search system that is just one text field that searches all the fields.

First question, do I need to create an additional table for keywords? If so, how do you keep that up to date? I get photos and information added every hour.

Do I need to look into a full text search or something different than the simple search/where?

Search The Whole Database
Is there a way to search all tables in a database for a keyword using 1 query?

Something like SELECT * FROM ??? WHERE ..ummm you know what,,I cant do it ,,please help

Database Search
I am looking for a query that will search all of my database without having to use all the individual table names and fields as there are 6 tables and over 30 fields in the database is there any special expression for this the search criteria would only be one word.

String Search In Whole Database
I want to search a string in whole database [not in a table]? Is it possible in MYSQL? If so then how?

Search Database Query
I am looking for a query that will search all of my database without having to use all the individual table names and fields as there are 6 tables and over 30 fields in the database is there any special expression for this the search criteria would only be one word.

How To Search Mysql Database Using Php
in my mysql database table, i have

1. "this is mysql practice search test"
2. "that was my mysql practice search test"

if i use **
"SELECT * FROM `table` WHERE `def` like '%mysql%' order by id"
it returns both rows but ..
if
"SELECT * FROM `table` WHERE `def` like '%mysql my%' order by id"
doesn't return anything.

how to search table with more than one word from that may be in same field or different field of any row of table.?

Very Basic Database Search
Have a very basic database of contacts that I want to make searchable via PHP. Have successfully constructed the database, and can connect and get a basic search, but not sure how to get what I want.

Table name: Business

Fields:
ID (key),Name,Address,City,State,Zip,County,Region,Phone,Fax,Email,Category (4 main categories),Type,Web,Contact

Vision for search page: unified search, possibly with Category field being main selection (checkboxes, maybe), then refining search by City, County, Region, Type only. Results will be set off in a <div> to the right.

Techniques Available To Search A Database
Can someone provide a list of the most common techniques used to search a database for matches based on user-entered keywords etc?

At this point the only SQL syntax I'm aware of to search a db is using the LIKE clause:

SELECT article FROM mytable WHERE article LIKE "php variable";

Is this an efficient enough way, even for a large table? For example, what kind of algorithm do commercial scripts like VB or blog use to search its content?

Database Search Approaches
can i put together a site search tool for a e-comerce site that draws from a mySQL database. They are currently using a tool (Swish-e) that relies on an index of their site.
I don't know a lot about searches & it got me wondering if it is faster to query the database directly or may a daily index and search that, like they are currently doing? I don't believe they get a ton of hits on their search engine. I think they get around 120 orders a day, if you can deduce anything from that.
I'm also curious about the flexibility I would have to modify the way the program searches in the future. If I were using an indexed version it seems like it might be hard to produce advanced searches (all <color> tv's from <manufactuer>)

Replace
If I need to replace word [one] with word [two] in a single table within one mysql database, how is this to be done?

Using REPLACE()
how to use "replace()". Basically I am looking for a way to tell replace to only perform the function if the pattern is at the beginning of the string. So...

I am attempting to rename every instance of "Nate" (in a column) to "Nate/Nathan"

UPDATE table SET column=REPLACE(column, "Nate", "Nate/Nathan");

That part works fine. It also works well if the value is "Nate/Nathanial", changing it to "Nate/Nathan/Nathanial"

What REPLACE() also does that I do not want it to do is take "John/Nate" and replace that with "John/Nate/Nathanial" I only want it to perform that search for "Nate" starting at the 0 index spot.

Because MySQL has another function called REPLACE (acts as an UPDATEDELETEINSERT all-in-one utility) I can't seem to find any information on replace().

REPLACE
I am trying to replace as follows :

SET @BEFORE='http://whatever.com/pn/index.php?name=PNphpBB2&file=viewtopic&t=';
SET @AFTER='http://whatever.com/forums/viewtopic.php?t=';
UPDATE table SET column =REPLACE(column,@BEFORE,@AFTER);

but this is not working correctly, maybe because of the question marks "?" and the & marks in the statements. (right?)

how could I escape the "?" ?

how can I make this "http://whatever.com/pn/index.php?name=PNphpBB2&file=viewtopic&t=" be read as a "simple text" expression to be replaced as I am trying to do?

REPLACE
Is there a such function where I can replace more than 1 thing in 1 function?
For example, I know this exists:
REPLACE(string, old_value, new_value)
But, is there any way where I can replace more than one thing...kinda of liek in a list
like old_value_1, old_value_2, new_value_1, new_value_2

Replace
i am trying to use replace on a table however the unique key is not a single column. How can i set the unique key to be a combination of two columns in the table?

Partial Replace
I have a field called ref_no containg data like this:

14a-12395

How would I replace the 14a- part to be left with 12395 ?

PS, the 14a could vary with the no of characters, ie it could be

9a-

or 124a-

Ive tried THIS, but it doesn't affect any rows:

update company_tbl_copy set ref_no = replace(ref_no,'%-','');

Replace ... Select?
I am trying to create a query that goes through a table of orders in the last 30 days and caches a list of the best selling items (and saves it to another table). I know that I can do this in two queries, but I am trying to force myself to use just one... The table I am saving is very simple -- product_id [unique, int(10)] and quantity_sold [int(10)].
My current query is:

MySQL
INSERT
INTO BestSellingProducts_Cache
( product_id
, quantity_sold
)
SELECT OrderItems.product_id AS OrderItems_product_id
, SUM(OrderItems.quantity) AS OrderItems_quantity_sum
FROM Orders
LEFT OUTER
JOIN OrderItems
ON Orders.id = OrderItems.order_id
WHERE Orders.orderdate >= ?'
GROUP
BY OrderItems.product_id
Unfortunately, since product_id is unique the second time I run this query I get a duplicate key error. I thought I might be able to append
MySQL
ON DUPLICATE KEY
UPDATE
SET quantity_sold = OrderItems_quantity_sum
But I can't do that when I use a GROUP BY in my SELECT.

I could easily empty the table before running the query, but since this will be running as a cron job, I wouldn't want the table left empty if there was ever a server hiccup or something and the second query dies. Is it possible to create a TRANSACTION with MyISAM and TRUNCATE?

is there a REPLACE INTO ... SELECT type query? None of these queries "age off" old data, but I think I would be okay with that... I'll have to run that spec by the bosses

REPLACE Vs. One To Many Relationships
To describe the problem, let me first present a hypothetical database, perhaps used to track students. This database consists of two tables.

The first table "students" has two attributes: a primary key called "ssn"(primary key unique not null) that tracks the student's SSN, and a second attribute "name" that tracks their name.

The second, "classes", also has two attributes, "fk_ssn"(not null references students(ssn)), and "class" (Varchar) which represents the classes they have taken. This essentially represents a one-to-many relationship.

The problem is this: I am doing updates to a similar database in php, inserting table by table. That is, I first insert into students, and then into classes. When using REPLACE syntax, MySQL will notice that the primary key in students exists already, and replace the existing record. However, since the classes.fk_ssn cannot be unique (recall: one to many), it cannot be a primary key, and thus, MySQL simply adds another duplicate record rather than replace the existing one.

My question is, does anyone know an elegant way to deal with this problem?

Replace Statement
I am trying to create a table and define a column using the replace statement but I get an error saying it is invalid syntax. The same syntax works okay in MSSQL, any ideas on what is wrong with the following syntax?

AC_KEY0 AS REPLACE(STR(AC_YEAR,004,00), ' ', '0')

Replace Id In Two Rows
I have a table where id is int and two simpe rows

+----+
| id |
+----+
10
+----+
15
+----+

and I want to replece id values in both rows. Now first row must be 15 and second 10

I can do this in 3 steps : 1. change in first row id =20 2. in second id=10 3. in fors row id=15(in my table more than 2 rows)The question is there function which do this in one step?

Replace Function
I am not an experienced user of MySQL and we are trying to produce a basic report but we are having trouble getting the replace function to work. We have a unique indentifer (contract id) from a finance package but need to delete the suffix "CS" from the contract id : we have used the following code:

REPLACE(ContractId, 'CS', ' ')

However, when we run our Crystal report the contractid is limited to a string of 6 yet we need to ensure that at least 10 char are returned.

Find And Replace
In entering information into my database, I've added a line of code into the main description field of every entry that I'm now having second thoughts about.

Is there a way I can do a find and replace operation on every description field to strip out the unwanted code?

Replace Word
I would like to know that is there any way to replace the particular word from the sentence by using Mysql query.

For e.g.->
I have records in the Mysql table likeâ€¦

CodeParticulars
101This is the $abc. I am $xyz for help.

In the above records, I would like to replace
$abc â€“ Help topic
And
$xyz â€“ searching

Find And Replace
In a field I will first FIND all instances of the following:

a DOT, a SPACE and ANY SMALL LETTER ie. ". a" or ". b" or ". z"

Then I will REPLACE this string with "SPACE SMALL LETTER" ie. I will remove the DOT.
Would you write the appropriate expression for this scenario?

Does Mysqldump Replace Everything?
I've made design changes to a test MySQL database on my local machine, and I would like to replace the MySQL database on my Internet server (which is also currently a test db).

Does the mysqldump command wipe out everything in the target database, and replace the *all* the objects (tables, indexes, relationships etc) and data?

Regexp, Replace ?
I am new to regular expressions and I need to know if they can help in solving this problem, I have a field in a table called "identification" which is a text field that includes dots (.), commas (,), hyphens (-) and letters, plus numeric characters. What I want to do is to extract ONLY the numeric characters from the field. Example of the field identification:

V-1.234.567, I would like as a result to obtain just 1234567. Any ideas on how to do this???

Find And Replace
I want to find Data and replace it. For example: I have a bunch of Data that looks like

356-A
357-A
358-A

I would like to be able to remove all the dashes without having to do it one by one in MySql. Any ideas?

Mysql Replace
I have a large database with text strings.

What is the quickest way to replace each ['] with a ['] throughout
the database fields while leaving everything else intact?

ex

.... I'm a monkey ...

becomes

.... I'm a monkey ...

etc.

Replace The First Occurence
Please help on the following:

I've the need to replace the first occurence of some text,
for example;

abcd///abcd:efg///

I need to replace the first abcd///
so that it displays abcd:efg///

anything in mysql query that has something similar to the php preg_replace?

Replace Query
I have a Mysql database using Phpmyadmin. I want to run a query on a table to replace some data. Let's call the table my_table and the fields field1 and field2. I need a query that searches my_table for instances where field1 is not identical to field2 and in the resulting instances replace field2 with field1.

Replace Function
I have two tables
I need to replace values 'Field61' and 'Field62' from table 'keyword' from source 'descripcion' and 'numero' from table capital where the FileID from table keyword is equal to FileID from table capital

y think on this

REPLACE INTO keyword (Field61)
SELECT descripcion
FROM capital C WHERE C.FileID = FileID --->(how do that this FileID be a keyword.FileID???)

Replace All Blanks
I don't know why I'm getting an error that says:
"Query returned no resultset."
I check the table and nothing was changed.

Here's my code:

UPDATE GFER2 SET part_num = REPLACE(part_num,'','0')

Regexp And Replace
I have a chemicals table with chem_id,chem_name, et al. I just received a large amount of new input which may or may not match the chemicals already in my table. I need to match the new chemical names against chem_name. I've already done a straight text match with some success. However chemical names are complex and contain many non-alphanumeric characters. I'd like to compare the new and old chemicals names with the odd characters and spaces removed. I know I could do a nested replace, but I was wondering if there were any easier way of cleaning the chem names?

Conditional REPLACE
suppose that there is a text like following (in datasore table)
Quote: "cookiedomain";s:11:"devshed.com"

i can change it using following query

UPDATE datastore SET data = REPLACE(data,
'"cookiedomain";s:11:"devshed.com"',
'"cookiedomain";s:0:""')

but there are somethings i do not know between quotes so i want create a query like "if there are somethings between quotes" namely "in between quotes are blank"

Replace Function
I am trying to do an update/replace function in MySQL but I am getting MySQL error #1064. Basically the query is just trying to update a field called Description in a table called Widgets by changing some values to a new value. So, for example, I want to change the field 'oldvalue123' to 'newvalue123'

The query works well in MS SQL 2000 but not in MySQL- Here is the query

Update Widgets
Set Description = (Select Replace(Description,'oldvalue','newvalue'))
From Widgets

REGEX In A REPLACE
PHP Code:

SELECT products_id, products_name, products_price, products_image,
LCASE(REPLACE(REPLACE(products_name, '&', 'and'), ' ', '_')) as seo_url,
FROM products
WHERE products_parent = {$this->_id}

Is it possible to replace the double REPLACE calls with some sort of Regular Expression? I'd like to make seo_url i.e.

"product's name" -> "products_name"

Mysql Replace
SELECT REPLACE(REPLACE(REPLACE (
Message,
"!FIELD1",
Field1
), "!FIELD2",
Field2), "!FIELD3",
Field3) AS RMessage
FROM customized, customized_messages
WHERE customized.ClientID = customized_messages.ClientID

anywong with it ? i got error....with the !FIELD

Replace/update
I used phpMyAdmin to build my MySql database. I entered the records in alpha order according to the primary key. I need to make minor spacing and formatting changes in these records. I still have the records stored in a text file exactly the way the records were cut and pasted into phpMyAdmin. I used the INSERT statement to insert the records in the database. Is there a command I can use with these records to copy over or replace the records I already have in the database by changing the INSERT statement to something else like UPDATE, REPLACE, CHANGE, REDO, COPY OVER or whatever it takes.

I can make the changes I need in the text file with a word processor, copy the records, paste the records into PhpMyAdmin like I did originally if I can find a way to replace the records that are already in the database.