CHARINDEX in reverse

Question

Post reply

CHARINDEX in reverse

Ken at work

Hall of Fame

Points: 3426
More actions
July 1, 2009 at 9:22 am

#73231

I need to find the first and last names (separately).
I am using:
Left(Job_Descript.PM,CHARINDEX(' ',Job_Descript.PM) - 1)
for the first name.
Is there a change to use CHARINDEX from the right.
Something to consider is that there is occasionally middle initials. If there wasn't I could simply start with the space and subtract from the LEN. But... the middle initial stops that idea.
Thank you,

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

do you want something like this?

declare @nam varchar(30)

set @nam='pradeep singh'

select left(@nam, charindex(' ',@nam)-1)

select right(@nam, len(@nam)-charindex(' ',@nam))

OUTPUT

------------

------------------------------

pradeep

(1 row(s) affected)

------------------------------

singh

(1 row(s) affected)

Pradeep Singh

Ken at work Hall of Fame Points: 3426 More actions · Answer 2

Is that going to work with the name: John Q. Public to give me:

First Name = 'John'

Last Name = 'Public'

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Have you tried this.

taking the above post as base code.

REVERSE(left(REVERSE(@nam), charindex(' ',REVERSE(@nam))-1))

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 4

you could also use this for the surname:

SUBSTRING(@nam,LEN(@nam) - charindex(' ',@nam),LEN(@nam))

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Ken at work Hall of Fame Points: 3426 More actions · Answer 5

I never thought of using a 'double reverse'

Thank you very much, that was great and works no problem.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 6

REVERSE is one of the less performant string functions in TSQL, so this alternative expression calls REVERSE only once.

SELECT RIGHT(@nam, CHARINDEX(' ', REVERSE(@nam)) - 1)

The expression above and also some of those in earlier posts will throw an error if the value of @nam has no spaces. If this is a possibility, you may want to use one of these more complex expression:

/* Returns entire string if there are no spaces */

SELECT RIGHT(@nam, COALESCE(NULLIF(CHARINDEX(' ', REVERSE(@nam)), 0) - 1, LEN(@nam)))

/* Returns empty string if there are no spaces */

SELECT RIGHT(@nam, COALESCE(NULLIF(CHARINDEX(' ', REVERSE(@nam)), 0) - 1, 0))

Ken at work Hall of Fame Points: 3426 More actions · Answer 7

It is going to take me a bit to figure why/how the Coalese works with the reverse, etc..

In the meantime since it saves on performance I have switched to it. Now comes the task of interpreting it.

I appreciate your help,

Thank you,

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 8

REVERSE is a fairly expensive function performance wise. No need to use three of them just to get the last name. I'll be back in a minute...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 9

Jeff Moden (7/2/2009)
REVERSE is a fairly expensive function performance wise. No need to use three of them just to get the last name. I'll be back in a minute...

Heh... I should read the whole post before I post... Andrew already did exactly what I would do... well, except on Tuesdays. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 10

Jeff Moden (7/2/2009) Andrew already did exactly what I would do...

I would not.

There was a member of Led Zeppelin, John Paul Jones.

Would not be happy seeing his name after such "parsing".

_____________
Code for TallyGenerator

nigel. SSChampion Points: 11636 More actions · Answer 11

Sorry, did the same as Jeff, didn't read Andrews answer and posted the same solution. :hehe:

Nigel
How to post questions

nigel. SSChampion Points: 11636 More actions · Answer 12

...twice !! dozy ******

Nigel
How to post questions

Terri-92562 SSCommitted Points: 1614 More actions · Answer 13

andrewd.smith (7/1/2009)
REVERSE is one of the less performant string functions in TSQL, so this alternative expression calls REVERSE only once.
SELECT RIGHT(@nam, CHARINDEX(' ', REVERSE(@nam)) - 1)

Bear in mind that this only returns the last name if the last name contains no spaces. My last name consists of 2 words separated by a space and it is parsing routines such as this that send me mail to the last half of my name or file me under the wrong letter. Of course the real problem is the that the name is not stored in separate fields to begin with.

Terri

To speak algebraically, Mr. M. is execrable, but Mr. C. is
(x+1)-ecrable.
Edgar Allan Poe
[Discussing fellow writers Cornelius Mathews and William Ellery Channing.]

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 14

Of course the real problem is the that the name is not stored in separate fields to begin with.

Agreed.