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