Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL to remove partial text from value

Tags:

sql

mysql

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

like image 357
1.21 gigawatts Avatar asked Jan 25 '13 18:01

1.21 gigawatts


People also ask

How do I remove the first 3 characters in SQL?

To delete the first characters from the field we will use the following query: Syntax: SELECT SUBSTRING(string, 2, length(string));

How do you match a partial text in SQL?

The SQL ANSI standard uses two wildcards, percent (%) and underscore (_), which are used in different ways. When using wildcards, you perform a SQL partial match instead of a SQL exact match as you don't include an exact string in your query. Note: denotes two spaces.

How can I replace part of a string in a column in SQL?

SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.

What will TRIM do in SQL?

By default, the TRIM function removes the space character from both the start and the end of the string. This behavior is equivalent to LTRIM(RTRIM(@string)) . To enable the optional LEADING , TRAILING , or BOTH positional arguments in SQL Server 2022 (16.


2 Answers

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
like image 131
Brian Webster Avatar answered Sep 28 '22 19:09

Brian Webster


In postgres this would be:

select substring(Version from 5 for 3) 
from table
where Version like '%WEB%'
like image 27
jdennison Avatar answered Sep 28 '22 21:09

jdennison