I have a column in my database with entries that starts or ends with a hyphen. I would like to find these rows and remove/replace the hyphens if it’s the last or first (or both) char in the string.
What I have now:
x
-sample-value-one-
sample-value-two-
-sample-value-three
sample-value-four
And this is what I would like to achieve:
sample-value-one
sample-value-two
sample-value-three
sample-value-four
So I should have to find all rows which starts or ends with a hyphen, and if there is a match, remove the hyphen while the other hyphens in the string should remain the same.
I don’t have problems with replacing one string, but this task seems to be too hard for me.
Advertisement
Answer
Assuming it’s just a single hyphen, then:
# Remove first hyphen
update myTable
set myCol = substr(myCol,2)
where myCol like '-%'
;
# Remove last hyphen
update myTable
set myCol = substr(myCol,1,length(myCol)-1)
where myCol like '%-'
;