Re: Strip list of characters from a field using SQL

To remove a specific list of characters as implied by the Subject:, rather than a list of characters to keep as implied by the quoted message text, then consider the following:

replace( translate( myField
, '//////////////////////////'
, '!@#$%^&*()_+[]{};:,.<>/?\|'
) /* slash redundantly included */
, '/'
, ''
) as RemovedChars

If spaces [blanks] are to be removed from the data, then the TRANSLATE scalar can rely on the implicit blank padding of the literal in the second argument:

replace( translate( myField
, ' '
, '!@#$%^&*()_+[]{};:,.<>/?\|'
)
, ' '
, ''
) as RemovedChars

Note: no consideration was made for anything but SBCS data in the above examples

An external UDF could be created, directed to a procedure that takes as input a VARCHAR argument that identifies the list of characters to be kept; eliminating all others. While a purely SQL function could be used instead, the inability to use a variable in the likely to be used SQL scalars [e.g. TRANSLATE] makes that less desirable. For example:

create function keepOnlyTheseCharacters
( inputString varchar(..)
, CharList varchar(..)
) returns varchar(..)
language rpgle ...
external name 'LIB/SRVPGM(ProcedureName)'

set "RESULT" =
keepOnlyTheseCharacters( myField
, 'abcdefghijklmnopqrstuvwxyz' )

Note: The procedure could implicitly effect equivalence for lower case and upper case, for any alphabetic characters; i.e. in order to match the implied effect for the mixed-case example in the OP.

Regards, Chuck

On 23 Aug 2012 10:00, Marc Couture wrote:

I would rather prefer which characters I would like to extract...

I.e. abcdefghijklmnopqrstuvwxyz. And drop all others characters.

On Aug 23, 2012, at 12:52, "Luis Rodriguez" wrote:

Using REPLACE? Something like:

REPLACE(myfield, '/', '')

On Thu, Aug 23, 2012 at 12:15 PM, Marc Couture wrote:

Possible stripping list of characters from a field using SQL ?

Example:

Marc/Couture. Result = MarcCouture