If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

How to parse comma seperated name

Started by wbarry123, 12 Oct 2010 10:55:34 AM

Previous topic - Next topic

wbarry123

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.

Lynn

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 */

wbarry123

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?

wbarry123

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)