1.21 gigawatts
1.21 gigawatts

Reputation: 17760

SQL to remove partial text from value

How would I write the SQL to remove some text from the value of records if it exists.

Version Column data
 ------------------  
WEB 1.0.1  
WEB 1.0.1  
1.0.2  
1.0.2  

I would like to update all records that contain "WEB " and remove that text but leave the rest, "1.0.1".

So far I have Select * from table.

Database is MySQL 5.5.25

Upvotes: 15

Views: 69522

Answers (4)

Art
Art

Reputation: 5782

Here's another example that should work in any SQL as functions used are ANSI SQL standard. This example assumes that there is a whitespace between word 'WEB' and first digit. But it can be improved. I think this is more generic approach then hardcoding start and end positions for subtr:

SELECT TRIM(SUBSTR('WEB 1.0.1', INSTR('WEB 1.0.1', ' ') ) ) as version 
  FROM dual;

 SQL> 

 VERSION
 -------
 1.0.1

Upvotes: 0

Brian Webster
Brian Webster

Reputation: 30855

The REPLACE feature of MySQL, SQL Server, and PostGres will remove all occurrences of WEB with a blank.

Selecting

SELECT REPLACE(Version, 'WEB ', '') FROM MyTable

Updating

UPDATE MyTable SET Version = REPLACE(Version, 'WEB ', '') 

or

UPDATE MyTable SET Version = REPLACE(Version, 'WEB ', '') WHERE Version LIKE '%WEB %'

Reference

  • REPLACE - SQL Server
  • REPLACE - MySQL
  • REPLACE - PostGres
  • I included multiple DB Servers in the answer as well as selecting and updating due several edits to the question

Upvotes: 30

Darren Kopp
Darren Kopp

Reputation: 77637

UPDATE Table
SET Version = Replace(Version, 'Web ','')
WHERE Version LIKE 'WEB %'

Upvotes: 0

jdennison
jdennison

Reputation: 2130

In postgres this would be:

select substring(Version from 5 for 3) 
from table
where Version like '%WEB%'

Upvotes: 1

Related Questions