RE: Strip list of characters from a field using SQL

Marc,

A brief Google search yielded this solution. I think you'll need to create a function like this one:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

While PatIndex('%[^a-z]%', @Temp) > 0
Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters(myfield)

Trevor Briggs
Analyst/Programmer
Lincare, Inc.
(727) 431-1246
TBriggs2@xxxxxxxxxxx
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Marc Couture
Sent: Thursday, August 23, 2012 1:00 PM
To: RPG programming on the IBM i / System i
Subject: Re: Strip list of characters from a field using SQL

Luis,

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

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

Thanks.

Sent from my iPad

On Aug 23, 2012, at 12:52, "Luis Rodriguez" <luisro58@xxxxxxxxx> wrote:

Marc,

Using REPLACE? Something like:

REPLACE(myfield, '/', '')

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Thu, Aug 23, 2012 at 12:15 PM, Marc Couture <couturem@xxxxxxxxxxx> wrote:

Hello All,

Possible stripping list of characters from a field using SQL ?

Example:

Marc/Couture. Result = MarcCouture

Regards,
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.