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

Question

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


UPDATE dbo.employees
SET
  lastname = listFirst(fullname, ","),
  firstname = listRest(fullname, ", ")

Please advise. Thank you.

user_1479535 · Answer

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 to make the strings safe for query.


    SELECT empID, fullname 
    FROM dbo.employees



    
        UPDATE dbo.employees
        SET
            lastname = ,
            firstname = 
        WHERE empID =

user_1970497 · Answer

This shouldn't be done in ColdFusion. This is a problem with the data and any data transformations should be handled on the SQL Server itself. There's no need to pull data back to CF, have CF manipulate the data, then pass the manipulated data back to SQL.

And as was mentioned in other comments, you are opening up a huge can of unintended worms by making this change. Names can be incredibly difficult to work with.

This answer relies on the fact that your name data consists of a last name, followed by a comma, followed by the rest of the name. It will trim the first and last names and remove the first comma, but again be wary of Garbage In, Garbage Out..

You can pass a query into the SQL like so:


    UPDATE dbo.employees
    SET lastname = ltrim(rtrim(left(fullname,charindex(',',fullname+',')-1)))
      , firstname = ltrim(rtrim(substring(fullname,charindex(',',fullname+',')+1,len(fullname))))

You can see it in SQL at https://dbfiddle.uk/?rdbms=sqlserver_2012&fiddle=85fc8abfdbb02c469e28c2e4dd1df4f5.

user_651924 · Answer

I think your issue is that you failed to put your CF functions in # signs and quotes.


UPDATE dbo.employees
SET
  lastname = '#listFirst(fullname, ",")#'
  , firstname = '#listRest(fullname, ", ")#'

You really should use though.


UPDATE dbo.employees
SET
  lastname = ''

As a best practice, I would sanitize variables BEFORE the SQL statement and use the trim function to get rid of spaces.


    firstname = trim(listFirst(fullname, ","));
    lastname = trim(listLast(fullname, ","));



UPDATE dbo.employees
SET
  firstname = ''
  , lastname = ''

Also, we have been trying to use SQL's DECLARE statement to define the variables there. This makes it really easy to give complex queries to your DBA and have them be able to work with them quickly. Also, many queries use the same variables multiple times so it's much easier and cleaner to declare them at the top of the list.


    firstname = listFirst(fullname, ",");
    lastname = listLast(fullname, ",");




    DECLARE @FirstName varchar(50) = 
    DECLARE @LastName varchar(50) = 

    UPDATE dbo.employees
    SET
      firstname = @FirstName
      , lastname = @LastName

user_8895292 · Answer

In addition to Shawn's answer, one important thing no one's mentioned is that you may be laboring under a misconception about queries in CF. One that could potentially have some very unpleasant consequences to the database, if not careful.

Being able to intermix CFML and SQL doesn't mean your database server executes CFML code

Databases servers don't know a thing about CFML. In fact, a database server never even sees any CFML code within a cfquery. All CFML (and/or cfcript) code is executed by the CF application server, before the database server even enters the picture.

So what actually happens when you intermix CFML and SQL? Conceptually, when the code executes:

The CF server attempts to evaluate any CF tags/functions and convert them into literal values. Those values are then concatenated with the rest of the plain text SQL, to form one big SQL string.
CF then hands that SQL string to the database for execution
The database server attempts to execute the SQL string and returns any results to the CF server
The CF server converts the database results into objects, like a "Query".

So as you can see, there's absolutely no mixing of the two. CF does it's job processing any CFML. The database handles any SQL. Never the twain shall meet.

What's the potential problem?

Take a query like this. It won't crash, but it won't produce the expected result either.




   UPDATE someTable
   SET    AccountNumber = '#listFirst(FORM.newValue, ",")#'

What actually happen is this. First, CF executes the list function which returns the literal string "1234".

   #listFirst(variables.newValue, ",")#

CF then plugs that literal into the rest of the SQL statement, producing this:

    UPDATE someTable
    SET    AccountNumber = '1234'

When sent to the database, that statement sets every single account number in the entire table to the same value: "1234". Almost certainly, not the desired result.

Understanding how CFML code is interpreted within queries can help avoid some truly unpleasant consequences to your database. Especially if you don't have recent backups...

user_7710857 · Answer

Listgetat will do the trick, for example;

listgetat(fullname,1)  
listgetat(fullname,2)

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

5 Answers

Being able to intermix CFML and SQL doesn't mean your database server executes CFML code

What's the potential problem?