split an attribute value and map them to firstname and lastname

[Abdul_Gaffar]

Hi ,

We are implementing a split function for a target value say "Abdul_Gaffar" to displayname and

Leverage the first half for the firstname and second half for the last name

Please find below the query we configured for split function.

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='workday'",
"UPDATE NEWUSERDATA SET DISPLAYNAME = substring(split(CUSTOMPROPERTY28,_))"
]
}

However, this is returning a syntax error.

Please suggest the syntax and the way to use the splitted value for firstname and lastname

Regards,

Abdul Gaffar

[sahil · Accepted solution]

Assuming the value for the attribute (email) is = abc@gmail.com. 

We need to extract the value before @ as Leftside and the value after @ as Rightside. You can use query as below

SELECT SUBSTRING_INDEX(email, '@', 1) as Leftside, SUBSTRING_INDEX(email, '@', -1) as rightside from users

Leftside	rightside
abc	gmail.com

[dgandhi · Accepted solution]

Below solution for your issue:

UPDATE NEWUSERDATA SET firstname = SUBSTRING_INDEX("Abdul_Gaffar", "_", 1)
--> This will give output as Abdul

UPDATE NEWUSERDATA SET lastname = SUBSTRING_INDEX("Abdul_Gaffar", "_", -1)
--> This will give output as Gaffar

Below link can help to prepare query

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_substring_index

Thanks

[Abdul_Gaffar]

Hi @sahil @dgandhi ,

Is the format below correct am facing json syntax error

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"firstname",
"lastname"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='ABC'",
"UPDATE NEWUSERDATA SET firstname =SUBSTRING_INDEX(CUSTOMPROPERTY28,'_',1)"
"UPDATE NEWUSERDATA SET lastname = SUBSTRING_INDEX(CUSTOMPROPERTY28,'_',-1)"
]
}

regards,

Abdul Gaffar

[PVoehrs]

Hi, There is a comma missing in the PREPROCESSQUERIES in the second line.

[sahil]

You may need to add Cp28 in ADDITIONALTABLES as well. 

Also, you can refer to the below link for example on Configuring the Preprocessor

https://docs.saviyntcloud.com/bundle/EIC-Admin-v23x/page/Content/Chapter03-User-Management/User-Impo...