MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » June 2014

Re: SQL TRANSLATE



fixed

No.

First off, you'd have to run it once for each AA value you want to replace.

Second, if the BBBBB value happened to include your AA value string, it'd
get replaced too. Not what the OP wanted.

Charles



On Fri, Jun 27, 2014 at 10:29 PM, Carel Teijgeler <coteijgeler@xxxxxxxxx>
wrote:

Should he not be thinking of REPLACE? As the length of the replacement is
longer than the original value.

Regards,
Carel Teijgeler


On 27-6-2014 23:32, John Yeung wrote:

On Fri, Jun 27, 2014 at 3:42 PM, Dave <dfx1@xxxxxxxxxxxxxx> wrote:

Looks like it would be
possible with Translate but even more unreadable.


Not even possible. That's just not how TRANSLATE works.

TRANSLATE maps individual characters to other single characters. If
you map, say 'xyz' to '123', then EVERY instance of 'x' in the string
to be translated becomes '1'. Every instance of 'y' becomes '2'.
More than one of these transformations can have the same destination
character. So 'xyz' can be mapped to '113' if you want. This will
turn every instance of 'x' into '1' and also every instance of 'y'
into '1'.

For example, the expression

translate('Hello, world!', '123', 'olH')

has the following components:

String to be translated: 'Hello, world!'
Map from: 'olH'
Map to: '123'
Resulting string: '3e221, w1r2d!'

This is nothing like what you're trying to do.

John

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact