SQLBase Replace Statement Help

Discussion forum about all things SqlBase.
oldschool
United States of America
Posts: 2
Joined: 04 Feb 2019, 17:31
Location: Eastaboga, USA

SQLBase Replace Statement Help

Post by oldschool » 05 Feb 2019, 18:02

Hello,

I'm looking for some help with removing double and single quotations from a description field in one of the tables in my SQLBase database. I'm using version 8.1.0 which is an older version of SQLBase and in looking at the language reference I'm thinking that I'll need to use some combination of the @REPLACE and @FIND function to accomplish this but I'm not quite finding the correct combination of syntax to accomplish this. Just so I'm clear on what I am trying to accomplish, I just want to replace the single and double quotes with a blank space and leave the rest of the description in place. The statements that I've been working on using the SQLConsole are as follows:

update part set description = @REPLACE(description, @FIND('\"',description,0), 1, ' ') WHERE description like '%\"';
update part set description = @REPLACE(description, @FIND('\'',description,0), 1, ' ') WHERE description like '%''';

I'm hoping this is possible using the SQLConsole because otherwise I'm faced with trying to edit the filed manually.

One additional caveat is that some of the descriptions may contain two or more double or single quotes.

Thanks for any help anyone might provide.

User avatar
Peter.Hugk
Germany
Posts: 103
Joined: 06 Mar 2017, 07:48
Location: Germany

Re: SQLBase Replace Statement Help

Post by Peter.Hugk » 06 Feb 2019, 11:22

There is just a wildcard missing in your where clause:
update part set description = @REPLACE(description, @FIND('\"',description,0), 1, ' ') WHERE description like '%\"%';

To replace multiple occurences you have to (re)start the script as long as the number of updated rows is greater than 0.

Regards,
Peter

oldschool
United States of America
Posts: 2
Joined: 04 Feb 2019, 17:31
Location: Eastaboga, USA

Re: SQLBase Replace Statement Help

Post by oldschool » 06 Feb 2019, 19:46

Thank you for the help. I ran this update statement on a test database and it replaced the first character in the description with a blank but didn't replace the double quotes. I'm going to look at this more to see if I can figure out how to accomplish what I'm trying to do but wanted to let you know that something is still off in the logic. Just to be clear this is what happened when I ran the update statement:

Before update description was LOOM MOUNTING CLIP 1/2"
After update description was OOM MOUNTING CLIP 1/2"

Thanks for giving this a look.

User avatar
Peter.Hugk
Germany
Posts: 103
Joined: 06 Mar 2017, 07:48
Location: Germany

Re: SQLBase Replace Statement Help

Post by Peter.Hugk » 07 Feb 2019, 11:28

Strange. I have an old script here where we used that too:
Update BETRAN Set BETRAN_ID = @REPLACE(BETRAN_ID, @FIND(' ', BETRAN_ID, 0), 1, '-') Where BETRAN_ID Like '% %';

As you can see we replaced spaces with minus characters. And this does work!

Regards,
Peter

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests