mysql add "prefix" to every value in column

Question

I need to add a 'prefix' in front of every value in a certain column.

Example: all fields in column x are: 200, 201, 202, 203, etc. I need them to be pn_200, pn_201, pn_202, pn_203, etc.

Is there a way to use ALTER or MODIFY commands to do this?

I would like something like ADD to BEGINNING of * column_name 'pn_'

Or perhaps a way to do it in PHP? Maybe get the value of the field, turn that into a variable, and do something like.

`$variablex = `'SELECT column_name FROM table'
$result = mysqli_query($con, variablex);
 foreach($r=mysqli_fetch_row($result) {
    `ADD TO BEGINNING OF * column_name 'pn_'`

Is there anyway to do that?

score 29 · Accepted Answer · answered Oct 16 '13 at 20:18

29

Actually it's even easier.

UPDATE table SET column_name = CONCAT('pn_', column_name)

Without a WHERE clause it will update all the rows of your table

answered Oct 16 '13 at 20:18

Machavity

30,841
27
92
100

this works great, thanks, very simple. Is there a way to remove the 'pn_' from every column? – Ghost Echo Oct 16 '13 at 20:23
3

Sure. Just as simple `UPDATE table SET column_name = REPLACE(column_name, 'pn_', '')` – Machavity Oct 16 '13 at 20:29

score 5 · Answer 2 · answered Oct 16 '13 at 20:17

5

SELECT concat('pn_', column_name) AS column_name
FROM yourtable

but why do this at the database layer? It's trivial to do it in PHP:

SELECT column_name ...

while($row = mysql_fetch_assoc($result)) {
   $data = 'pn_' . $row['column_name'];
}

answered Oct 16 '13 at 20:17

Marc B

356,200
43
426
500

Very creative method, but I prefer to change the database because the naming conventions I'm working with have changed. For future use, I want everything to do be uniform. When I add things to the database, I don't want to wonder if it already has a 'pn_' or not, as there are multiple variables. Great idea though! – Ghost Echo Oct 16 '13 at 20:30
sounds like a bit of a stupid naming convention. it's not like the system can "forget" which field a bit of data came from, unless you're passing through some dumb serialization/transmission medium that would sever the db/data relationships like that. – Marc B Oct 16 '13 at 20:32
No. I want to add a prefix to some of my rows. It's not stupid. When I do a mysql search I want to be able to tell right there, in my result, if it has the 'pn_' prefix, or perhaps a different prefix, 'du_', me_', 'as_' whatever. I don't want to have to open a php file every time I want to see which prefix was added. – Ghost Echo Oct 17 '13 at 12:40
1

but why modify the actual data, you can always do `select 'pn', yourfield` and keep the prefix separate from the real data, saving you having to strip it off again afterwards. – Marc B Oct 17 '13 at 13:45

Jose Samaniego · Answer 3 · 2013-10-16T20:26:56.807

1

i think this is what you want

$que = "SELECT column_name FROM table";
$res = mysql_query($que, $con);
if(mysql_num_rows($res)>0){
while($row = mysql_fetch_array($res)){  

echo "PN_". $row['column_name'];

}
}

if you only want to show it wit pn_ at the beginnning but if you want to change it also in the database you need to select all get the id value and update it with concatination

edited Oct 16 '13 at 20:26

answered Oct 16 '13 at 20:21

Jose Samaniego

110
9

score 0 · Answer 4 · answered Oct 16 '13 at 20:17

0

UPDATE MyTable
SET MyField = CONCAT('pn_', MyField)

answered Oct 16 '13 at 20:17

Linger

14,942
23
52
79

2

mysql uses `concat()`, you're doing mathematical addition. – Marc B Oct 16 '13 at 20:17

mysql add "prefix" to every value in column

4 Answers4