concat three attributes and get initials of middle name

[AbdulGaffar]

Hi , 

We need to populate the display name for the users as (lastname , middle name initials , first name) 

below is the preprocessor query to populate the same 

however , I am facing a syntax error please suggest the correct syntax

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20","DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='Workday'",
"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(lastname,',substring(middlename,1),',',firstname)"
]
}

[nimitdave]

Please try as:

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='Workday'",
"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(lastname,',',ifnull(substring(middlename,1),''),',',firstname)"
]
}

[nimitdave]

{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,lastname,middlename,firstname FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"DISPLAYNAME"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='Workday'",
"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(lastname,',',ifnull(substring(middlename,1),''),',',firstname)"
]
}

[dgandhi]

Try below

UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(lastname,', ',ifnull(substring(middlename,1),''),',',firstname)"

You can use below URL to prepare the SQL query

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

Thanks