Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Preprocessing Query to remove suffixes

sairamya15
New Contributor III
New Contributor III

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

7 REPLIES 7

NM
Valued Contributor II
Valued Contributor II

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
All-Star
All-Star

@sairamya15  try below

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star

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


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Saathvik
All-Star
All-Star

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

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

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

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


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

Yes, working on 24.4 but not on 24.3