3

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
Improve this question
2
  • The syntax seems fine to me. However, you should use the where clause on the update statement as well. Commented Jun 15, 2015 at 14:43
  • 2
    Like this: update [question_table] set [option] = replace([option],'cm2','cm<sup>2</sup>') WHERE [option] LIKE '%cm2%' Commented Jun 15, 2015 at 14:45

4 Answers 4

Reset to default
4

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.

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

Comments

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%'
Improve this answer

3 Comments

it is giving me an error. please see the updated question.
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.
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.
0

This should work, tested in Transact SQL:

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

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.