Library data was imported from the spreadsheet. The author's name is listed in the form "FirstName LastName".

We want to change the author from "FirstName LastName" to "LastName, FirstName".

One simple SQL command could modify the value of the author data field. The command uses the position of the last space (space character before the last name). If the entry has space characters at the end or front of the entry, it could generate errors. Instead, we can execute the TRIM command to remove the leading and trailing spaces.

UPDATE LIBRARY_NEW SET AUTHOR = TRIM(' ' FROM AUTHOR)

We are ready to change the format of the AUTHOR data field. However, before we do it, we can run a test and check the result. In the test, we take the value of the author data fields, process it, and save it in CUSTOM2.

UPDATE LIBRARY_NEW SET CUSTOM2 = SUBSTRING(AUTHOR from CHAR_LENGTH(AUTHOR)-(POSITION(' ' in REVERSE(AUTHOR))-2) for POSITION(' ' in REVERSE(AUTHOR))) || ', ' || SUBSTRING(AUTHOR from 1 for CHAR_LENGTH(AUTHOR)-POSITION(' ' in REVERSE(AUTHOR)))

Review the test results.

review result, AUTHOR:

Now, we can safely change the format of the entries in the AUTHOR data field.

UPDATE LIBRARY_NEW SET AUTHOR = SUBSTRING(AUTHOR from CHAR_LENGTH(AUTHOR)-(POSITION(' ' in REVERSE(AUTHOR))-2) for POSITION(' ' in REVERSE(AUTHOR))) || ', ' || SUBSTRING(AUTHOR from 1 for CHAR_LENGTH(AUTHOR)-POSITION(' ' in REVERSE(AUTHOR)))

You can change the value of the AUTHOR using CUSTOM2 from the test.

UPDATE LIBRARY_NEW SET AUTHOR = CUSTOM2

Use the CUSTOM VIEW feature: you can define a SELECT statement to review results before you execute the SQL command that modifies data values. The SELECT definitions create and display a temporary table.