CHARINDEX question

[AlexCuse]

Hi All,

I am wondering if there is a way to return CHARINDEX reading from right to left. Here is my problem, I have to take last names from a name field where field can be populated in any of these ways:

J. J. Doe
J. Doe
John Doe
John J. Doe

And I haven't come across a J. John Doe but it would not surprise me if I did.

What I want to return is the Last name, or everything past the Right most space in a column. Is there any way I can tell SQL to do this, or am I going to have to do it the ugly way? I have not yet completely conceived the ugly way yet, but I know it will come to me on the drive home.

A million thanks to anyone who can help me avoid taking it that far, as this name is going to be used to match back to three other tables and the ugly way seems like it could lead to an exceptionally long query. Thanks in advance for your time on this one.

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[gmmastros]

Charindex with Reverse shold get you started.

Select reverse('J. John Doe')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Use the REVERSE function.

select REVERSE(SUBSTRING(REVERSE([i]column[/i]), CHARINDEX(' ', REVERSE([i]column[/i])), LEN([i]column[/i])))
from [i]table[/i]

I may have missed a bracket in there. You'll want to check it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[AlexCuse]

Thanks guys, at first it was returning some crazy stuff because I had reverse all over the place, but I managed to get it down to this:

select ltrim(right((a.[Name]),charindex(' ',(REVERSE(a.[Name])))))
from AllSeminarPC a

It's certainly not pretty, but it does the job beautifully :-D

I am glad that I know about this REVERSE function now, although at the same time I hope I never have to use it again.

Thanks again, and have a wonderful evening!

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

I just realized there are Jr's in there! That will make it a fun morning...

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.