Replace queried mysql results with other values


This query will replace values in a single column with pre-defined values:

PHP Code: selectUser_ID,casewhenAnswer_Number_1=1then'cat'whenAnswer_Number_1=2then'dog'whenAnswer_Number_1=3then'fish'endasanswer
fromtablenamewhereUser_ID='whatever';


That works great for 1 column, Answer_Number_1. But, if I have 25 Answer_Number_# columns, it doesn't work so well.

Every value for an Answer_Number column will be a 1,2,or 3 so I need something that will account for all the columns and output cat, dog, or fish instead of 1,2, or 3.

Any suggestions would be greatly appreciated.
Posted On: Monday 29th of October 2012 05:55:22 AM Total Views:  176
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Auto_increment replacement

hi, what databases do not support auto_increment and what can we use instead of auto_increment for them
VIEWS ON THIS POST

116

Posted on:

Friday 12th October 2012
View Replies!

Can i use str_replace in UPDATE statement

Is it possible to incorporate the str_replace function within an UPDATE statement The reason i ask is i have a database containing details of my music scores. I have managed to create my own interface whereby i can search for records and edit any of the fields as i wish. It works faster and looks better than the actual interface that comes with it. Say, i had entered a search for 'Jimi Hendrix' and had spotted a typo error and discovered all the entries referred to 'Mimi Hendrix'. If there were only a couple of records, i could quickly remedy the situation. But, if there were some forty entries it would be quite laborious. Is it possible to search for, and then update all rows containing the Hendrix entries in one go. Presumably using the two tools i mention 'UPDATE' and 'str_replace'
VIEWS ON THIS POST

84

Posted on:

Friday 12th October 2012
View Replies!

replace a substring in several record

Hi eveybody I have table called songs that have a field called genres with values like: latinmusic/salsa/mujer.mp3 englishmusic/rock/myguitar.mp3 latinmusic/salsa/ayer.mp3 latinmusic/salsa/eslavida.mp3 I have over 20 records with the substring "latinmusic/salsa/" and I want to replace it with the substring "latinjazz/" to vave this result: latinjazz/mujer.mp3 englishmusic/rock/myguitar.mp3 latinjazz/ayer.mp3 latinjazz/eslavida.mp3 Does any one know a hoe to change all the substrings at once
VIEWS ON THIS POST

97

Posted on:

Friday 12th October 2012
View Replies!

Import csv, special chars replaced or lost

, I have received an excel file. I save this file as csv format. I have tested both MS-Dos csv, and delimiter csv. I have tried character set UTF8 and latin1 Then I do with a load file into the import: Code: LOAD DATA INFILE 'c:\\xxxxxxxxxx.csv' INTO TABLE ledenlijst CHARACTER SET UTF8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (`bndc`,`vlet`,`vnam`,`tnam`,`anam`,`gesl`,`gdat`,`adat`,`strt`,`stnr`,`stnt`,`pstc`,`wplt`,`mail`,` mtel`,`htel`,`temc`,`antr`) The import itself works fine but characters like: - is lost - is replaced by or %! (depends char set/csv) - is replaced by another char If I make a dump of the datbase then is this result of create the database: CREATE DATABASE `db_name` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; Type: MyISAM As anybody an idea how to solv this problem
VIEWS ON THIS POST

163

Posted on:

Friday 12th October 2012
View Replies!

Problem using replace and convert. help plz.

what is wrong with my statementhelp plzzzz. UPDATE mytable SET myfield= convert(replace(convert(myfield,char),'2','5'),integer); produces ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for1 the data type for 'myfield' is integer. I am trying to replace all 2's to 5. for example, if the integer is 222, i want to change it to 555.
VIEWS ON THIS POST

94

Posted on:

Friday 12th October 2012
View Replies!

Search replace x with y in table column

I think this may work, but I wanted to check before I do this. I want to replace all instances of & with & in a column in a table. Will the following work Code: UPDATE yourtablename SET yourcolumname = REPLACE(yourcolumnname,'&','&')
VIEWS ON THIS POST

107

Posted on:

Friday 12th October 2012
View Replies!

To replace a single character in all 6000+ records

I want to replace an Arabic Unicode character to an Urdu one in all of my 6000+ records. Is it possible to do so using a single query
VIEWS ON THIS POST

108

Posted on:

Friday 12th October 2012
View Replies!

replace with a double quote in the texte

ok my line : Code: UPDATE table SET field = replace(field,"","") I want to replace Code: by how can I modify the quote in "subtitle" to make the replace work
VIEWS ON THIS POST

105

Posted on:

Sunday 21st October 2012
View Replies!

find and replace SQL Query Help

i need a code to replace "text1" with "text2" in all database.
VIEWS ON THIS POST

104

Posted on:

Sunday 21st October 2012
View Replies!

Trying to use "replace" but double quote error

UPDATE x SET y = REPLACE(y, 'style="cursor: url('/', 'style="cursor: url('http://google.com/') Basically i'm trying to replace style="cursor: url('/ with style="cursor: url('http://google.com/ When I do this i get the following error There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem ERROR: Unclosed quote @ 75 STR: " SQL: UPDATE x SET y = REPLACE(y, 'style="cursor: url('/', 'style="cursor: url('http://google.com/') Basically, I think I need to figure out how to escape the double quotes
VIEWS ON THIS POST

96

Posted on:

Sunday 21st October 2012
View Replies!

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. Any suggestions
VIEWS ON THIS POST

91

Posted on:

Monday 22nd October 2012
View Replies!

Text string find and replace with variable text?

I recently migrated my blog from Blogger to Wordpress. My posts database contains about 1000 entries. The post_content (text string) field of this database contains backlinks to previous posts that look like this: So, I just need the ".html" changed to "/" in about 180 instances. Any ideas on how I could run a find and replace that would retain the unique YEAR, MONTH, and slug values in each of these links Also, I'm a total SQL noob, so you might need to spell it out for me...
VIEWS ON THIS POST

111

Posted on:

Wednesday 24th October 2012
View Replies!

Duplicate + Find and replace string

I have 2 goals for this query: 1) I have multiple records with a source = '7'. I need those records duplicated and replaced with source = '12', and '13'. ---So any records in source7, are now in source 12,13, and 7. 2) the CID's for each source start with that digit. i.e anything in source 12, are 12001, 12003, 12014, etc. ----So anything with source 12, SHOULD NOT have a cid of 7004, 7008, etc. The query i have pretty much does the job, EXCEPT for the CID's...it'll find ANY 7 and replace with 12, so i'll get weird CID's when there is a 7 located in the middle of a number. (i.e 7077 which SHOULD change to 12077, it changes to 1201212 - because it finds EVERY '7' in that string). If i can just change it to change the FIRST 2 digits in the CID, WHERE source's FIRST digit = '7'. Overall goal: To duplicate source 7, to source 12, and source 13 - while changing the CID's accordingly, and not making my new cid's all weird everytime it finds a 7. my query: insert into sm_school (cid, name, status, priority, category, virtual_number, source, notes, url, de_filler, rr_active, de_virtual_number, max_cap_active, hs_grad_year, min_age, max_age, min_credits, other_filters, al, ak, az, ar, ca, co, ct, de, dc, fl, ga, hi, idd, il, inn, ia, ks, ky, la, me, md, ma, mi, mn, ms, mo, mt, ne, nv, nh, nj, nm, ny, nc, nd, oh, ok, orr, pa, ri, sc, sd, tn, tx, ut, vt, va, wa, wv, wi, wy, undergrad, graduate, military, filter, generated, min_college) SELECT (replace(cid, 7, 12)), name, status, priority, category, virtual_number, 12, notes, url, de_filler, rr_active, de_virtual_number, max_cap_active, hs_grad_year, min_age, max_age, min_credits, other_filters, al, ak, az, ar, ca, co, ct, de, dc, fl, ga, hi, idd, il, inn, ia, ks, ky, la, me, md, ma, mi, mn, ms, mo, mt, ne, nv, nh, nj, nm, ny, nc, nd, oh, ok, orr, pa, ri, sc, sd, tn, tx, ut, vt, va, wa, wv, wi, wy, undergrad, graduate, military, filter, generated, min_college from sm_school where source = '7'
VIEWS ON THIS POST

127

Posted on:

Wednesday 24th October 2012
View Replies!

Database wide character replacement

hi, is it possible to do database wide character replacement in mysql thing is that i need to replace certain characters in every field of every table, say to replace every occurrence of Ă with č i need to do this because i got problems while transferring database from one server to another thanks for you help , no, there is no easy way with sql, you would have to write an update statement for each column much easier would be to do a text edit search/replace on the transfer file
VIEWS ON THIS POST

117

Posted on:

Wednesday 24th October 2012
View Replies!

Search and replace on entire database?

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"); Can anybody tell me how to do a search and replace on an entire database in all fields
VIEWS ON THIS POST

88

Posted on:

Wednesday 24th October 2012
View Replies!

Quick help for MySQL replace in PhpMyAdmin

You would use REPLACE to do this. You can find it in the string functions area of the manual.
VIEWS ON THIS POST

81

Posted on:

Wednesday 24th October 2012
View Replies!

How to replace multiple line breaks

I'm trying to replace multiple line breaks in a field with a double line break, but only when there are 3 or more in a row. So for example I would want to replace \n\n\n with \n\n and \n\n\n\n with \n\n etc. I'm trying this but it doesn't seem to work Code: UPDATE xf_post SET message = REPLACE(message, '\n\n\n', '\n\n')
VIEWS ON THIS POST

90

Posted on:

Thursday 25th October 2012
View Replies!

IN, ANY, SOME subqyery LIMIT replacement

Here's my current query: Code: SELECT p.*, u.username FROM photos AS p INNER JOIN users AS u ON u.id = p.user_id WHERE p.id IN ( SELECT id FROM photos p WHERE user_id = p.user_id LIMIT 3 ) ORDER BY p.id DESC LIMIT 30; What this query is supposed to do is: select 30 photos from the "photos" table with a condition that there are at most 3 photos from the same user (or value "id" in the "users" table). The problem with this query is that I'm using MySQL 5.1.33 which doesn't yet support LIMIT clause in IN, ANY or SOME subqueries, so the above query doesn't work. Is it possible to modify this query to work in MySQL 5.1.33 , Just to let you know, rudy, I have already solved this problem with query like this (thank you stackoverflow.com): Code: SELECT u.username, p1.* FROM photos AS p1 INNER JOIN users AS u ON u.id=p1.user_id LEFT OUTER JOIN photos AS p2 ON p2.user_id=p1.user_id and p2.id
VIEWS ON THIS POST

94

Posted on:

Thursday 25th October 2012
View Replies!

BETWEEN replace

Hi all. I can't use the BETWEEN clause so the old query Code SQL: SELECT * FROM categories WHERE lft BETWEEN '1' AND '6' ORDER BY lft ASC; the new query Code SQL: SELECT * FROM categories WHERE lft >= '1' AND lft
VIEWS ON THIS POST

74

Posted on:

Thursday 25th October 2012
View Replies!

How to regex replace part of a string?

Hi Forum How to replace the beginings- and the ends of string without any change of the string in between An example: this is my input: col1 H1, verylongstring1... ,H H2, verylongstring2... ,H and this should be my output after replace: col1 P1, verylongstring1... ,P11 P2, verylongstring2... ,P22 . So far i can select col1 by: SELECT col1 FROM table1 WHERE col1 REGEXP 'H1.*H'; but in : UPDATE table1 SET col1 = REPLACE( col1, REGEXP 'H1.*H', REGEXP'P1.*P11'); the REGEXP does not work !
VIEWS ON THIS POST

76

Posted on:

Monday 29th October 2012
View Replies!