CODEWITHSUNDEEP
mysqlsqlsql-update
lakshmen
lakshmen

Reputation: 29094

Update part of a string in SQL

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

Upvotes: 3

Views: 9235

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82534

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.

Upvotes: 3

Dportology
Dportology

Reputation: 808

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.

Upvotes: 0

haslo
haslo

Reputation: 1079

This should work, tested in Transact SQL:

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

Upvotes: 0

Christopher Gibson
Christopher Gibson

Reputation: 68

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%'

Upvotes: 1

Related Questions