Update part of a string in SQL

Question

I would like to update a part of string in a particular column of my database.

I have cm2 in the particular column of my database and would like to update it to cm2.

My sql query to find the cm2 is:

SELECT * 
FROM  `question_table` 
WHERE  `option` LIKE  '%cm2%'

To update I want to try is this:

Update question_table.option
set Value = replace(value,'cm2','cm<sup>2</sup>').

Not sure whether this is correct. Need some guidance on this.

EDIT:

Syntax:

UPDATE question_table SET option = Replace(option, 'cm2','cm<sup>2</sup>')
WHERE option LIKE  '%cm2%'

It throws me an error as:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option = Replace(option, 'cm2','cm<sup>2</sup>') WHERE option LIKE '%cm2%'' at line 1

The syntax seems fine to me. However, you should use the where clause on the update statement as well. — Zohar Peled, Jun 15 '15 at 14:43
Like this: `update [question_table] set [option] = replace([option],'cm2','cm²') WHERE [option] LIKE '%cm2%'` — Tingo, Jun 15 '15 at 14:45

score 3 · Accepted Answer · edited May 23 '17 at 12:05

3

The reason you get an error is probably since option is a reserved word in MySql. to escape reserved words in MySql use this char `:

UPDATE question_table 
SET `option` = Replace(`option`, 'cm2','cm<sup>2</sup>')
WHERE `option` LIKE  '%cm2%'

Here is a list of reserved words in MySql

Here is my favorite method of avoiding the use of reserved words.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 15 '15 at 15:15

Zohar Peled

79,642
10
69
121

score 1 · Answer 2 · answered Jun 15 '15 at 14:46

1

Your guess looks almost correct. I just tried your code in SQL Server and it works a treat.

UPDATE table SET field = Replace(field, 'string to replace','replace with this')
WHERE field LIKE  '%filteredby%'

answered Jun 15 '15 at 14:46

Christopher Gibson

68
9

it is giving me an error. please see the updated question. – lakshmen Jun 15 '15 at 14:55
I'm using SQL Server as I didn't see the database type in your original post. I found this on another post for MySQL: REPLACE(telephone, " ", "") LIKE "%0208%" The likely problem is the single quotes vs. double quotes. I think the replace syntax looks the same. – Christopher Gibson Jun 15 '15 at 14:57
Looking at the error it looks like it might have interpreted some of the data as a character code or something. Why does the error mention '^2' instead of 'cm2'? I'd try creating a new table with a single field and attempting to get the replace working on that first, then it'll probably become obvious. – Christopher Gibson Jun 15 '15 at 15:14

score 0 · Answer 3 · answered Jun 15 '15 at 14:49

0

This should work, tested in Transact SQL:

UPDATE question_table SET option = REPLACE(option,'cm2','cm<sup>2</sup>')

answered Jun 15 '15 at 14:49

haslo

1,030
2
12
23

score 0 · Answer 4 · answered Jun 15 '15 at 14:49

Just do a basic update statement to replace entries in columns that have certain values.

UPDATE 'question_table'
SET 'option' = "cm2"
WHERE 'option' = "cm2"

As for entering a superscript, you would need to make sure your column supports Unicode characters, so you would need an NVARCHAR column. Here's a table of ASCII codes where you can find the codes for the superscripts 1, 2 and 3.

link: http://www.theasciicode.com.ar/

The NCHAR function in SQL Server can insert these characters for you (i.e. NCHAR(0x2082) replaced with whatever code you wish to use) but it may be different depending on which SQL implementation you are using.

Update part of a string in SQL

4 Answers4