× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.