Geek asks and answers

SQL to remove partial text from a value

How can I write 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 containing "WEB" and delete this text, but leave the rest "1.0.1".

Still with me Select * from table.

Database is MySQL 5.5.25

+5
source share
4 answers

The REPLACE MySQL , SQL Server and PostGres function will delete all occurrences WEBwith an empty one.

The choice

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

Update

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

or

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

Link

+13

postgres :

select substring(Version from 5 for 3) 
from table
where Version like '%WEB%'
+1
UPDATE Table
SET Version = Replace(Version, 'Web ','')
WHERE Version LIKE 'WEB %'
0
source

Here is another example that should work in any SQL as the functions used - this is the ANSI SQL standard. This example assumes that there is a space between the word "WEB" and the first digit. But it can be improved. I think this is a more general approach, and then the start and end positions of hardcoding for subtr:

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

 SQL> 

 VERSION
 -------
 1.0.1
0
source

More articles:


All Articles