remove character on sql-CodePudding

I have a table

i want to remove characters with prefix 'J' with substr

this my sql code, but still not working

SELECT SUBSTR(name,2) FROM table_name WHERE name LIKE 'J%'

CodePudding user response：

The query you wrote says "show me all the names that needed to be modified", but what I think you want is, "Show me ALL the names, but names that follow a particular pattern I want you to modify first."

The job of modifying the data in the results is the responsibility of the SELECT statement. You've touched on it yourself with your use of SUBSTR. So you want to pull all the names, but only change some of them. Further study into the catalog of MySQL string operations reveals a dizzying array of options. The goal is "SELECT name but if name starts with 'J' then chop that off."

For educational purposes, I encourage you to try to implement this with IF logic, but ultimately that's not necessary.

And while the ultimately powerful regex functions are tempting, there's a simpler option, TRIM. TRIM allows you to say, "chop this string from the front and/or back of this other string."

Since you want all results, there is no WHERE clause anymore, and your query is simply

SELECT TRIM(LEADING 'J' FROM name) FROM table_name

Look at that. FROM meaning two different things. No one ever said SQL was pretty.

If your actual use-case is trickier than simple TRIM can handle, there's a whole bunch more functions to peruse, and ultimately there's regex.

CodePudding user response：

You can able to check others forms to use TRIM function in a following link:

TRIM Functions