The TRANSLATE SQL Scalar function is the equivalent for the %XLATE RPG
function.
Single quotes also have to be doubled
Select Translate(a.Bfname, ' ', '",''')
From ...
Translate can only replace single characters with another single characters,
while with Replace different lengths can be replaced, for example:
Replace(Text, '%', 'Percent') or Replace(Text, '-', '')
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
mlazarus
Sent: Freitag, 20. März 2020 01:46
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Using SQL to replace a quote in a character string
Dave,
It's not SQL, but the %Xlate() BIF can handle multiple characters getting
translated at once.
-mark
On 3/19/2020 4:34 PM, dlclark@xxxxxxxxxxxxxxxx wrote:
"MIDRANGE-L"<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on
03/19/2020
04:18:25 PM:
I need to replace double quotes (") and commas (,) in strings with a
space
I know I can use replace
Select REPLACE(a.bfname, ',', ' ') as New_Bfname from file a Select
REPLACE(a.bfname, '"', ' ') as New_Bfname from file a
My predicament is to replace quotes (') in strings with a space Any
idea how that can be achieved?
AND
Is there a way to incorporate ALL the changes in one go
A regular expression would be cleaner, but you can do the
following:
Select REPLACE(REPLACE(REPLACE(a.bfname, ',', ' '), '"', ' '), '''', '
') as New_Bfname from file a
Sincerely,
Dave Clark
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
