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?

Clarenceclarenceux

Answer 1

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

Observant

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

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

Answer 2

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'];
}

Dug

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! – Clarenceclarenceux

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. – Dug

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. – Clarenceclarenceux

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. – Dug

Answer 3

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

Prophylactic

Answer 4

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

Tound

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