Zen Zen - 1 year ago 88
SQL Question

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.

Answer Source

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"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download