Preprocessing Query to remove suffixes

[sairamya15]

Hi All,

We have a requirement to remove suffixes from LASTNAME below are the Preprocessing query which we have used which is not working as intended kindly suggest if you have any inputs.

1.

"UPDATE NEWUSERDATA SET NEWUSERDATA.LASTNAME = REGEXP_REPLACE(NEWUSERDATA.LASTNAME, '( jr\\.$)|( jr$)|(Jr\\.$)|( Jr$)|( JR\\.$)|( JR$)|( Sr\\.$)|( Sr$)|( SR\\.$)|( SR$)|( sr\\.$)|( sr$)|( III$)|( II$)|( I$)|(IV$)|( Iv$)|( V$)|( v$)|( 3rd$)', '')"

2. UPDATE NEWUSERDATA SET LASTNAME = REPLACE(REPLACE(NEWUSERDATA.LASTNAME, 'Jr', ''),'Sr','')- this is removing only 2 characters like if we have "JR." it is removing "JR" and  '.' is still in Lastname.

Even tried: https://forums.saviynt.com/t5/identity-governance/require-inputs-on-preprocessing-script-to-fetch-da...

[NM]

Hi @sairamya15 ,

Try this 

UPDATE NEWUSERDATA SET LASTNAME = REPLACE(REPLACE(NEWUSERDATA.LASTNAME, ' ', ''),'.','')

I believe you have to remove special character ".," n all from lastname.

[Raghu]

@sairamya15  try below

"UPDATE NEWUSERDATASET LASTNAME = REPLACE(REPLACE(REPLACE(REPLACE(NEWUSERDATA.LASTNAME, 'Jr.', ''), 'Sr.', ''), 'Jr', ''), 'Sr', '')"

[rushikeshvartak · Accepted solution]

UPDATE NEWUSERDATA
SET LASTNAME = TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(LASTNAME, ' Jr.', ''), ' Jr', ''), ' jr.', ''), ' jr', ''), ' JR.', ''), ' JR', ''), ' Sr.', ''), ' Sr', ''), ' sr.', ''), ' sr', ''),
' SR.', ''), ' SR', ''), ' III', ''), ' ii', ''), ' II', ''), ' i', ''), ' I', ''), ' IV', ''), ' v', ''), ' V', ''), ' 3rd', ''))

[Saathvik]

@sairamya15 : REGEXP_REPLACE is supported in MySQL 8.0 since you are on 23.7 I assume your database is in MySQL 5.x please confirm the same. 

Incase if you have space b/w last name and suffix consistently for all like( xxxx JR, ssss SR, bbbb II etc) then use condition as below

UPDATE NEWUSERDATA SET LASTNAME = SUBSTRING_INDEX(SUBSTRING_INDEX(NEWUSERDATA.LASTNAME, " ", -1), ".",1)

[gauravchandok]

Its not working on v24.3 as well but working on other env v24.4

[rushikeshvartak]

24.4 comes with Mysql 8 . Raise support ticket to validate database version

[gauravchandok]

Yes, working on 24.4 but not on 24.3