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?

Machavity · Accepted Answer · 2013-10-16 20:18:13Z

37

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, 2013 at 20:18

Machavity♦

31.8k27 gold badges97 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ghost Echo Over a year ago

this works great, thanks, very simple. Is there a way to remove the 'pn_' from every column?

Machavity Over a year ago

Sure. Just as simple UPDATE table SET column_name = REPLACE(column_name, 'pn_', '')

Marc B · Accepted Answer · 2013-10-16 20:17:20Z

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, 2013 at 20:17

Marc B

362k44 gold badges433 silver badges508 bronze badges

4 Comments

Ghost Echo Over a year ago

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!

Marc B Over a year ago

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.

Ghost Echo Over a year ago

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.

Marc B Over a year ago

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.

Jose Samaniego · Accepted Answer · 2013-10-16 20:26:56Z

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, 2013 at 20:26

answered Oct 16, 2013 at 20:21

Jose Samaniego

1109 bronze badges

Comments

Linger · Accepted Answer · 2013-10-16 20:17:04Z

0

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

answered Oct 16, 2013 at 20:17

Linger

15.1k23 gold badges54 silver badges79 bronze badges

1 Comment

Marc B Over a year ago

mysql uses concat(), you're doing mathematical addition.

Collectives™ on Stack Overflow

mysql add "prefix" to every value in column

4 Answers 4

2 Comments

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related