SQL to remove partial text from value

Question

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

@Kostia, RTFM comments are not welcome. @1.21 gigawatts - You might also be interested in using `REPLACE` to update records as seen here: http://stackoverflow.com/questions/1876762/mysql-way-to-update-portion-of-a-string — Brian Webster, Jan 25 '13 at 18:32

Brian Webster · Accepted Answer · 2013-01-25T18:42:54.053

30

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

edited Jan 25 '13 at 18:42

answered Jan 25 '13 at 18:20

Brian Webster

30,033
48
152
225

OK. I created a row with the value in the field is "TEST 1.0.1". I tested with REPLACE(version, 'TEST ', '') and it said it affected 9000 rows but it doesn't seem to have changed anything. (refreshed the table) – 1.21 gigawatts Jan 25 '13 at 18:32
2

Your question was about `SELECTING` rows, if you want to `UPDATE` rows, it is different. I will edit my answer to show the difference. – Brian Webster Jan 25 '13 at 18:33

score 1 · Answer 2 · answered Jan 25 '13 at 18:20

1

In postgres this would be:

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

answered Jan 25 '13 at 18:20

jdennison

2,000
2
15
22

same function exists for postgres – jdennison Jan 25 '13 at 18:21

score 0 · Answer 3 · edited Jan 25 '13 at 18:41

0

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

edited Jan 25 '13 at 18:41

Brian Webster

30,033
48
152
225

answered Jan 25 '13 at 18:22

Darren Kopp

76,581
9
79
93

score 0 · Answer 4 · answered Jan 25 '13 at 19:57

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

SQL to remove partial text from value

4 Answers4