Jump to content

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

Russian Wyatt 2

Recommended Posts

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


Smith Smith

Link to comment
Share on other sites

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.


TABLE FILE ggsales


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');




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

Link to comment
Share on other sites

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:


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



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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...