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
To delete the first characters from the field we will use the following query: Syntax: SELECT SUBSTRING(string, 2, length(string));
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.
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.
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.
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
In postgres this would be:
select substring(Version from 5 for 3)
from table
where Version like '%WEB%'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With