How can I add text to SQL Column

Question

I want to update 1 column in SQL Table. Example: Current value in column is like this

2013/09/pizzalover.jpg 
2013/10/pasta.jpg

Now i want to update whole column like this : www.mypizza.com/2013/09/pizzalover.jpg Is there any way I can accomplish this? Thanks in advance

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using (please update tags accordingly).... — marc_s, Sep 29 '13 at 18:35
this worked great for me update oc_product set sku= CONCAT('BBC', sku); but how can I make it so it only adds to the column when there is no occurrence of the text BBC in the column for any that are added after the initial SQL run. as every day new ones are added on auto and I can't change the way they are added. thanks in advance from anyone who can help. so this my best option to add to the sku of every product. — Annette Kennedy, Jun 26 '21 at 12:42

score 14 · Accepted Answer · answered Sep 29 '13 at 18:37

14

You can simply update column using statement

update TableName set ColumnName  = 'www.mypizza.com/' + ColumnName

answered Sep 29 '13 at 18:37

Satpal

132,252
13
159
168

1

Be careful of using "+" your db instance might interpret the following string as a number and truncate the whole thing. Use concat instead if your db supports it – Anders Larsen Jan 07 '21 at 01:29

heretolearn · Answer 2 · 2013-09-30T03:50:36.083

If you are using MYSql, you can use the concat() as :

update tableName set columnName= CONCAT('www.mypizza.com/', columnName);

SQLFiddle

If you are using oracle you can use the concatenation operator '||' as :

update tableName set "columnName"='www.mypizza.com/'||"columnName";

SQLFiddle

In SQL Server you can use + for string concatenation as:

update tableName set name='www.mypizza.com/'+columnName;

SQLFiddle

score 2 · Answer 3 · answered Sep 29 '13 at 18:35

You mean like this?:

SELECT 'www.mypizza.com/' + ColumnName AS ColumnName FROM TableName

Depending on the rest of your application environment, there is likely a much better way to accomplish this. But in terms of just using SQL to add static text to a column in a SELECT statement, you can just concatenate the text directly in the statement.

Or, if you wanted to UPDATE the column values, something like this:

UPDATE TableName SET ColumnName = 'www.mypizza.com/' + ColumnName

Same principle, just using an UPDATE instead of a SELECT, which will modify the underlying data instead of just modifying the view of the data.

That is non-standard SQL. The concatenation operator in standard SQL is `||` not `+` (`+` is for adding numbers) — , Sep 29 '13 at 18:37

score 2 · Answer 4 · answered Oct 08 '19 at 16:11

2

OP doesn't specify which DBMS they are using. The following is for Postgres to update a text column by adding a prefix to it (tested with PostgreSQL v11):

UPDATE my_table 
SET column_1  = 'start_text_' || column_1
WHERE column_1 LIKE 'prefix_%'
;

answered Oct 08 '19 at 16:11

Lucio Mollinedo

2,295
1
33
28

score 1 · Answer 5 · edited Aug 15 '17 at 16:24

First get the information stored in your database and then edit it, you can do that like this:

<?php 
$query = "SELECT * FROM  `blog-posts`  WHERE `id` = 11";
$result = mysql_query($query);
$post = mysql_fetch_array($result);
$title = $post['title'];
$title .= "aapje";
echo $title
?>

And then update your database like normal:

$updateq = "UPDATE `blog-posts`  SET `title` = '$title' WHERE `id` = 11";

score 1 · Answer 6 · answered Apr 01 '23 at 00:29

If you use SQlite3, you may|| oparator.

For Ex:

update Diodes set KICAD_SCHLIB = "ORELTEK_lib:" || "Library Ref" where "Library Ref" not in  ("Resistor" ,"Capacitor", "ELEC_Capacitor", "Inductance", "Feridbeed", "STD_Diode", "Schottky_Diode", "LED", "TVS_Diode", "Zener_Diode")

How can I add text to SQL Column

6 Answers6

Linked