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

Saviynt and MYSQL string functions with REGEXP_SUBSTR()

New Contributor III
New Contributor III

Hello everyone,

I would like to know if there is any way to being able to use REGEXP_SUBSTR() and this kind of string functions in saviynt. 

Need to interpret an complex string for account matching, which becomes easier with regexp options available.


{value=2222daf4-2222b-222d-a222-cd2fe222d222, type=custom, customType=}, {value=E123456, type=organization}


For matching the account I need to have the "E12345" value extracted.



Valued Contributor
Valued Contributor

@Volker , have you tried it in analytics ? I have used reges and substring separately and those works.

Not sure on REGEX_SUBSTR(). If it doesn't work you can achieve above via case, regex and substring combo.



New Contributor III
New Contributor III

@AmitM , nope it is not supported in analytics as well. Have now worked around the question and split it into 2 queries, as I can not ensure that the order in the json will stay stable:

lower(concat('abcdef','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-1),', type=organization}',1))#lower(concat('abcdef','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-2),', type=organization}',1))

This statement works in data analyzer, and extracts me the wanted information "123456" from the provided JSON String.

Unfortunately even if the result should match the account adoption does not work so far.

Can you explain requirement with example and input data

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

Hi @rushikeshvartak ,

please excuse the late response. 

Here the situation and what should be archived 


For the account with E123456 all is working. On the other hand the account admins123456 gets not adopted to the user E123456.

The full adoption rule looks like the following:



lower(username)=lower(accounts.customproperty27)#lower(customproperty1)=lower(accounts.customproperty29)#lower(email)=lower(accounts.customproperty29)#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-1),', type=organization}',1))#lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(SUBSTRING_INDEX(accounts.customproperty27,'value=',-2),', type=organization}',1))



I walked trough each single SQL query separately in the data analyzer and found that for the admins123456 account two matches will happen, but they do not result in an adoption. 

Here my steps

  • Rule part to be  checked:
    lower(concat('admin','s',substring(lower(username),2)))=lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))
  • User object side of rule 4:
    • User with attribute username='E123456' was used for this step 
    • lower(concat('admin','s',substring(lower(username),2)))
    • The resolution will start from the innermost part
      • lower(username)
        • lower('E123456')
        • 'e123456'

      • substring(lower(username),2)
        • substring('e123456',2)
        • '123456'

      • concat('admin','s',substring(lower(username),2))
        • concat('admin','s','123456')
        • 'admins123456'

    • Final result: admins123456

  • Account side of the rule 4:
    • running it for account with name='admins123456'
    • accounts.customproperty29
    • 'admins123456'@**********.com' 

    • lower(SUBSTRING_INDEX(accounts.customproperty29 ,'@',1))
      • SUBSTRING_INDEX(accounts.customproperty29 ,'@',1)
        • SUBSTRING_INDEX('admins117625@********.com','@',1)
        • 'admins123456''

    • Final result: admins123456


  • Final Result of part 4 of correlation query is:
    • 'admins123456' = user side = account side = 'admins123456'' 

From my understanding it is an match of that account to the specific user and should result in an successful adoption.

Do I miss something?


[This message has been edited by moderator to mask email]