RE: SQL: find pattern and replace with blanks

Chuck's post while seeming to provide a solution, IMHO just points out how fragile trying to do this
using LOCATE is going to be.

A better option, a UDF that uses regular expressions....

Take a look at these two articles:
http://www.itjungle.com/mpo/mpo102303-story01.html
http://www.itjungle.com/mpo/mpo100903-story01.html

Download the java class:
http://www.itjungle.com/mpo/mpo100903-sansoterra.zip


Create the UDF:

CREATE FUNCTION mylib.re_ReplaceAll(input VARCHAR(500),
regex VARCHAR(500),
Replace VARCHAR(500))
RETURNS VARCHAR(500)
EXTERNAL NAME 'UDFs.re_ReplaceAll'
LANGUAGE Java
PARAMETER STYLE Java
FENCED
NO SQL
RETURNS NULL ON NULL INPUT
SCRATCHPAD
DETERMINISTIC


Then use it:

Select re_ReplaceAll(myfld, '\d{2}/[A-Z]{2}/[A-Z]{3}',' ')
From mytable

(note my regex is quite a bit rusty, so double check the above :)


You could also build your own RPGIV re_ReplaceAll function to use as the UDF. Scott Klement wrote an
article for System i Network about using regular expressions in RPGIV. (System I network's web site
is down but here's a RPG-L posting from Scott)
http://archive.midrange.com/rpg400-l/200405/msg00152.html

I assume Scott's code takes advantage of the ILE C/C++ runtime functions:
Compile regular expression regcomp()
Execute compiled regular expression regexec()
Free memory for regular expression regfree()
Return error message for regular expression regerror()


HTH,

Charles Wilt
--
Software Engineer
CINTAS Corporation - IT 92B
513.701.1307

wiltc@xxxxxxxxxx

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, May 07, 2008 8:04 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL: find pattern and replace with blanks

The original sample row, plus a few more sample rows for consideration:
....+....1....+....2....+....3
"DOE, JOHN A 12/AL/PMZ "
"DOE, JOHN A 12/AL/PMZ JON "
"DOE, JONATHON A12/AL/PMZ "
"DOE, JONATHON ALBERT12/AL/PMZ"
"DOE, JOHN/J A 12/AL/PMZ "

Prior to issuing an update, analysis of the data by selection which
determines any exceptions to all assumptions would be prudent. For
example, for the [possible] assumptions that:
- one blank precedes the pattern ex. 3 & ex. 4
- only blanks follow the pattern ex. 2
- no slash precedes the pattern ex. 5

Note: The following expressions assume the LIKE selection is enforced
for every row that is included in the result set. That is, no logic
that would include rows where that LIKE test was false; e.g. beware of
any OR logic. Additionally if a "/" may occur in a name, a creative
TRANSLATE may be required to ensure desirable results, since the first
slash is inferred by the following expressions, to be the third
character of the located pattern.

The given selection would include rows where the field had non-blank
characters following the expected nine character pattern. However if
the trailing zero-to-many characters are known to always be blanks, then
the following expression should suffice:
set namefield = substr(namefield, 1, length(rtrim(namefield))-9)

The above expression should effect the desired, even if there may be
no blank preceding the pattern, such that the pattern selection were
changed to be instead:
LIKE '%__/__/___%' /* original: LIKE '% __/__/___%' */

*However*, the effect of the above expression would probably be
undesirable for a row where non-blank characters followed the pattern.
Refer to the second of the sample rows, where the result would be "DOE,
JOHN A 12/A", which means the pattern was *not* replaced by blanks.

If there may be non-blank characters following the pattern, and those
characters should also be replaced with blanks [i.e. beyond just that
pattern being replaced by blanks], then as stated, the above assignment
can not effect the desired. Instead use the LOCATE [, POSSTR, or
POSITION] function to infer the start position of the pattern:
set namefield = substr(namefield, locate('/',namefield)-3)

If there may be non-blank characters following the pattern, and those
characters must _not_ be replaced with blanks [i.e. replace the pattern
only, leaving any suffix unchanged], then a bit more work is required.
In that case use the LOCATE [, POSSTR, or POSITION] function to infer
the start position of the pattern, plus both the CONCAT and the SUBSTR
functions, to concatenate the substrings on either side of the pattern.
For example:
set namefield = substr(namefield, 1, locate('/',namefield)-3)
concat substr(namefield, locate('/',namefield)+7))

Note: The given expressions as assignments were not tested for
accuracy, neither in syntax nor function.

Regards, Chuck

Dan wrote:

Time to hit the SQL guru well again. ;-)

I have a name field that has, as an example,
"DOE, JOHN A 12/AL/PMZ "

The "12/AL/PMZ" will always occur after the name (can't guarantee
the number of spaces separating the two), and will always follow
the pattern "nn/AA/AAA", where "nn" is a 2-digit number, and
"AA/AAA" is always upper-cased alpha. When and if such a pattern
is found, I need to replace it with blanks.
The way I would search for this would use: LIKE '% __/__/___%'
Even though the underscores represent any single character, I can't
imagine I would ever find it in a real name in my data. But even
if I use this, how would I then replace the string with blanks?

FWIW, this is on V5R4.

--
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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.