How can I split a “Lastname, Firstname” data into separate lastname and firstname columns?

  • A+
Category:Languages

We have a table with a column called fullname with the format of "Lastname, Firstname"

I added new columns to the table so we can separate the lastname and the firstname, but I can't figure out how to go about it. The code below is what I used but "listFirst is not a recognized built-in function name."

<cfquery name="splitname" datasource="dsn"> UPDATE dbo.employees SET   lastname = listFirst(fullname, ","),   firstname = listRest(fullname, ", ") </cfquery> 

Please advise. Thank you.

 


One simple way (this will not be the best way to do this. But it looks like an one time thing. So it should be fine.) is to select the rows as a separate query and update each row in a loop. Also you will need to evaluate the list functions before passing them to the query. Also use <cfqueryparam> to make the strings safe for query.

<cfquery name="splitname" datasource="dsn">     SELECT empID, fullname      FROM dbo.employees </cfquery>  <cfloop query="splitname">     <cfquery datasource="dsn">         UPDATE dbo.employees         SET             lastname = <cfqueryparam value="#trim(listFirst(splitname.fullname, ","))#" cfsqltype="varchar">,             firstname = <cfqueryparam value="#trim(listlast(splitname.fullname, ","))#" cfsqltype="varchar">         WHERE empID = <cfqueryparam value="#splitname.empID#" cfsqltype="varchar">     </cfquery> </cfloop> 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: