CODEWITHSUNDEEP
mysqldatabasereplace
user1397417
user1397417

Reputation: 738

how to remove trailing or leading charaters in a mysql query?

I have a Japanese dictionary table, the English words that correspond to the Japanese look something like this:

jap       eng                   marks     id
テクスト    /  text/ text book/     (n)     31112

I want to remove the leading and trailing slashes and spaces. it almost always have leading and trailing slashes but also have valid slashes separating the multiple English meanings. How can i write a query that will remove a given string if it is leading or trailing? In this case the "/ " is leading and "/" is trailing but sometimes there is multiple slashes on the end or front and sometimes with or without unnecessary spaces

so im thinking if I had some query like 

set eng = REPLACE_LEADING_OR_TRAILING(" /", "", eng)

i could just query it with all the possibilities 

REPLACE_LEADING_OR_TRAILING("//", "", eng)
REPLACE_LEADING_OR_TRAILING(" /", "", eng)
REPLACE_LEADING_OR_TRAILING(" / ", "", eng)
REPLACE_LEADING_OR_TRAILING("/ ", "", eng)
REPLACE_LEADING_OR_TRAILING("/", "", eng)

i know about the REPLACE() function but i can't see how i would do it with that because of the valid slashes in between multiple English meanings.

Upvotes: 0

Views: 977

Answers (1)

ESG
ESG

Reputation: 9435

You want to use the TRIM function:

To remove spaces, just call it with no other parameter than your field/variable

SELECT TRIM(eng)

To remove another character, use this version instead:

SELECT TRIM(BOTH '/' FROM eng);

Upvotes: 2

Related Questions