TSQL help

[Laura]

Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura

[jkv]

Hi Laura,
try this:
firstname = substring(name, 1+(charindex(',', name)), 25),
lastname = substring(name,1,(charindex(',',name)-1))


------------
Laura at 6/5/00 5:20:50 PM

Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura

[Laura]

Hi JKV,

Thanks for your help! However, I got this message when running the script:

Msg 536, Level 16, State 1
Invalid length parameter passed to the substring function.

It's caused by the lastname field. Any Ideas?


------------
jkv at 6/6/00 4:20:43 AM

Hi Laura,
try this:
firstname = substring(name, 1+(charindex(',', name)), 25),
lastname = substring(name,1,(charindex(',',name)-1))


------------
Laura at 6/5/00 5:20:50 PM

Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura

Thanks Deepak!!! That worked!


------------
Deepak at 6/6/00 5:08:52 PM

hi,

lastname = select substring( colname , 1, charindex(',', colname,1) -1 ) firstname = select ltrim(substring(colname,charindex(',', colname,1) + 1, len(colname)- len(substring( colname, 1, charindex(',', colname,1) -1 ))))

Deepak.


------------
Laura at 6/6/00 9:56:10 AM

Hi JKV,

Thanks for your help! However, I got this message when running the script:

Msg 536, Level 16, State 1
Invalid length parameter passed to the substring function.

It's caused by the lastname field. Any Ideas?


------------
jkv at 6/6/00 4:20:43 AM

Hi Laura,
try this:
firstname = substring(name, 1+(charindex(',', name)), 25),
lastname = substring(name,1,(charindex(',',name)-1))


------------
Laura at 6/5/00 5:20:50 PM

Hi,

I am trying to break up a column "name" into 2 columns "first name" and "last name". The name colum is currently "lastname, firstname". Is there an easy way to do this in SQL 6.5?

I successfully extracted the first name using:

firstname = rtrim(substring(name, (charindex(',', name)), 25))

But I am having trouble doing the lastname. Please help.

Thanks so much!
Laura