I have a data item that is formatted like "Last Name, First Name". I want to parse this into 2 separate columns. How do I go about this? I am on 8.3 and MS SQL.
It is always best if this type of thing is done in the database where possible, but if you are stuck with parsing it, you can look up the equivalent syntax in MS SQL for the below logic.
Note that variations in how the data is populated can screw things up for you.
substring(theNameField, 1, locate(',', theNameField)-1) as last_name
/* Substring from the first character up to one position before the comma to get last name */
substring(theNameField, locate(',', theNameField)+2, length(theNameField)) as first_name
/* Substring from two positions after the comma (assuming comma always followed by space) to the end to get first name */
Thanks for the quick response. I am getting the following error: "locate" is not a recognized built-in function name.
Is there a sql server equivalent function?
Got it figured out. Using MS SQL Server
first name: substring([Name], charindex(',', [Name])+2, char_length([Name]))
last name: substring([Name], 1, charindex(',',[Name])-1)