Trim spaces

[Deam]

I need to extract the lastname without any suffixes.
E.g- I want Carlson instead of
Carlson,Jr or
Carlson Jr. or
Carlson III etc.

anyone...

 

[foxdev]

LEFT(MyNameColumn, CHARINDEX(' ', MyNameColumn))

Robert Bradley

http://www.foxdev.com/

 

[Deam]

That did not return anything !:-D

 

[foxdev]

Well, no, not if you only typed in the part I replied with, of course. Is that what you mean? Or that no records were returned?

Be sure that between the quotes there is a single space, or you could use a comma. In other words:

[tt]select LEFT(MyNameColumn, CHARINDEX(' ', MyNameColumn)) as LastName from MyTable[/tt]

[tt]select LEFT(MyNameColumn, CHARINDEX(',', MyNameColumn)) as LastName from MyTable[/tt]

If your column contains mixtures of both, then you might have to apply both.

Robert Bradley

http://www.foxdev.com/

 

[Deam]

Oops I am sorry, that did work, the result was about 1500 rows and the rows which had last name trimmed were at the bottom for which i had to scroll down. Thanks.LOL

 

[jballum]

Should it not be the following:

LEFT(MyNameColumn, CHARINDEX(',', MyNameColumn)-1)

This way the ',' is not included in the output.

JB