CODEWITHSUNDEEP
mysqlsql
dreamend
dreamend

Reputation: 79

SQL Update - Changing a string

I have table like this: 

Users(id,name)

I need a query to find name's start with 'asdf_' and update all rows with removing this prefix.

for example:

Before query, the table contains: 

asdf_john
asdf_jane

After query I want:

john
jane

Thanks for help

Upvotes: 2

Views: 246

Answers (4)

Abhay
Abhay

Reputation: 6645

This should work by removing the starting "asdf_" only from all names that start with "asdf_"

UPDATE `Users` 
SET `name` = SUBSTRING(`name`, 6) 
WHERE `name` REGEXP '^asdf_';

Upvotes: 0

manji
manji

Reputation: 47978

Update Users
set name = trim(leading 'asdf_' from name)
where name like 'asdf_%'

Upvotes: 1

Shef
Shef

Reputation: 45589

UPDATE `Users` SET `name` = replace(`name`, 'asdf_', '') WHERE `name` LIKE 'asdf\_%' ESCAPE '\';

Added the WHERE clause based on @UltraCommit's answer, so it replaces when it finds the string at the beginning.

OR as based on @McPepper's answer:

UPDATE `Users` SET `name` = SUBSTRING(`name`, 6) WHERE `name` LIKE 'asdf\_%' ESCAPE '\';

If you want to replace only when the string is at the beginning. Replace the number 6 with the number of the characters the string you want to replace has + 1. In this case your string asdf_ is 5 characters long, +1 it becomes 6. That's the character position where the DB will start keeping the string.

Upvotes: 0

BastiS
BastiS

Reputation: 452

The sql-function you could use is Substring: SUBSTR (Example: http://www.1keydata.com/sql/sql-substring.html)

For your table USERS:

UPDATE USERS SET NAME = SUBSTRING (NAME, 6) WHERE NAME LIKE 'asdf_%';

Upvotes: 6

Related Questions