20

I have a few rows in a test database where there are dollar signs prefixed to the value. I want to UPDATE the values in the name row of the test1 table however when I threw the following query together it emptied the six rows of data in the name column...

UPDATE test1 SET name=overlay('$' placing '' from 1 for 1);

So "$user" became "" when I intended for that column/row value to become "user".

How do I combine UPDATE and a substr replacement without deleting any of the other data?

If there isn't a dollar sign I want the row to remain untouched.

The dollar sign only occurs as the first character when it does occur.

Improve this question
2
  • 4
    name = replace(name, '$', '') Commented Nov 26, 2014 at 15:05
  • @a_horse_with_no_name Thank you, that works. Not enough reputation to up-vote unfortunately though I'm curious how to do this if the dollar sign is the first character only? I tried this and it replaced dollar signs regardless of position. I tried UPDATE test1 SET name = replace(name, '$', '', from 1 for 1); without success; if you please post it as an answer I'd be happy to accept it. Commented Nov 26, 2014 at 15:14

2 Answers 2

Reset to default
38

If you want to replace all dollar signs, use this:

update test1 
   set name = replace(name, '$', '');

If you want to replace the $ only at the beginning of the value you can use substr() and a where clause to only change those rows where the column actually starts with a $

update test1 
    set name = substr(name, 2)
where name like '$%';
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

That was interesting, I hadn't considered using substr, I'm guessing it's not possible to combine replace with FROM then?
@user312854: no idea. I never got used to that form of the replace() function. For more complex replace tasks I usually use regexp_replace()
I can't think of any instances, at least offhand, where I'd need to consistently replace a character at a specific point between the first and last characters though I imagine that replace and FROM would be more efficient than using a regular expression? I'm fairly good at regex though avoid it unless I really have to to cut down on server load.
0

To answer the question using the pattern the OP had in mind.

UPDATE test1 SET name=overlay(name placing '' from 1 for 1)
WHERE name like '$%';
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.