I have a name data field that needs at least five alpha char...

[Russian Wyatt 2]

I have a name data field that needs at least five alpha characters (field will eventually be part of a Key field), but some of the names Im reading in has 2-4 characters.

Is there an easy way other than using checking the length and using concat to add filler characters

Also if the incoming data field has a special character, ( like a apostrophe or dash ) is there an easy way to replace that with the letter X

For example:

Last Name KEY name

D'Von DXVon

Wu WuXXX

Smith Smith

[Martin Yergeau]

DEFINE FILE abc

NewFld /A5 = RPAD(REPLACE(REPLACE(<yourFieldName>, ''', 'X'), '-', 'X'), 5, ' ');

END

 

image.png706613 18.4 KB

 

Also available LPAD with same syntax

See : Using WF Function

[David Beagan]

In the 8207 functions manual are some new ones:

REGEX: Matching a String to a Regular Expression

REGEXP_COUNT: Counting the Number of Matches to a Pattern in a String

REGEXP_INSTR: Returning the First Position of a Pattern in a String

REGEXP_REPLACE: Replacing All Matches to a Pattern in a String

REGEXP_SUBSTR: Returning the First Match to a Pattern in a String

 

Using Functions 8207.27 REGEXP_REPLACE Page 172

This seems like a good situation to use one, the regex replace function.

SET PAGE=OFF

TABLE FILE ggsales

PRINT SEQ_NO

COMPUTE Last_Name/A5 = DECODE SEQ_NO (1 'Wu' 2 'Smith' 3 'DO$$r' 4 'A*B' 5 'ab&' 6 '#dave' ELSE 'D''von' );

COMPUTE New_Name/A5 = REGEXP_REPLACE(Last_Name, '[''$"#*& ]', 'X');

WHERE READLIMIT IS 7

END

 

The middle parameter is a list of characters, enclosed within square brackets, that are to be substitued with an X. The single quote is represented as two single quotes. Also, there is a space character in the list which handles filler characters at the end (but it doesnt work if it is a varchar: Last_Name/A5V ).

[Russian Wyatt 2]

Unfortunately production is still on 8.2.06, so I cant use the REGEX functions just yet. I did however get it to work using a tweaked version of what MartinY posted:

DEFINE

LName/A5 = RPAD(REPLACE(REPLACE(REPLACE(LastName, '''', 'X'), '`', 'X'), '-', 'X'), 5, 'X');

END

 

I added a single quote replacement as well just to compensate for user data entry style, Ive seen people use the single quote and the apostrophe ( and ` ) in the same context . This is far cleaner than what I originally came up with on my own. Thanks.

[David Beagan]

By the way, the can be either an apostrophe or a single quote, I guess it depends how you use it.

The ` is a grave accent. I believe it does not have any specific use in WebFOCUS. But you can certainly replace it in a LastName.