CODEWITHSUNDEEP
mysqlsqlstring
Zen
Zen

Reputation: 77

Remove characters from beginning of a string in SQL

I am running a gaming community that used to have a requirement of having a prefix in members usernames. An example would be "RDxBuffalo". We recently decided to get rid of the prefix/tag and now I am looking to remove it from everyone's name. I am assuming the best way of doing this would be via a SQL query and I have been having issues finding the best way to do it.

This is what I am looking for the query to do:

  1. Check all usernames in database for any that have "RDx" (some may or may not be capitalized) at the beginning of the name/string.

  2. Remove the "RDx" from the beginning of the username.

Any help is appreciated, and an explanation of the query would be amazing.

Upvotes: 1

Views: 5576

Answers (3)

Nate Vaughan
Nate Vaughan

Reputation: 3839

Per the MySQL documentation, the function you are looking for is SUBSTR() and the operation to modify a table is UPDATE. You can use the SUBSTR() function in your WHERE clause to find rows that match your criteria:

UPDATE your_members_table 
SET member_name = SUBSTR(member_name, 4) --drops first 3 characters
WHERE SUBSTR(member_name, 1, 3) = "RDx"

Good luck!

Edit: Add UPPER() or LOWER() for case-insensitivity:

WHERE UPPER(SUBSTR(member_name, 1, 3)) = "RDX"

Upvotes: 7

anil
anil

Reputation: 668

You can do it using an update query.

The where part of the query will be as follows 

    where lower(name) like 'rdx%'

The update part you can use substring functions such as mid(). 

     MID(column_name,start)

Upvotes: 1

Shadow
Shadow

Reputation: 34231

Check the first 3 chacarcters in the where clause using the left() function and convert them to lower case with the lower() function, use the substring() function in the set clause to return the string from the 4th character:

update yourtable set username=substring(username,4) where lower(left(username,3))='rdx'

Convert the above statement to a select to see how it works.

Upvotes: 1

Related Questions