Re: Strip list of characters from a field using SQL

[CRPence <CRPbottle@xxxxxxxxx>]

  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