Reputation: 17760
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
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
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
Upvotes: 30
Reputation: 77637
UPDATE Table
SET Version = Replace(Version, 'Web ','')
WHERE Version LIKE 'WEB %'
Upvotes: 0
Reputation: 2130
In postgres this would be:
select substring(Version from 5 for 3)
from table
where Version like '%WEB%'
Upvotes: 1