CHARINDEX in reverse

Question

CHARINDEX in reverse

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 1

Sergiy (7/5/2009)
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".

Sergiy is actually the most correct on this. Full name parsing is dangerous at best especially considering that you can have 3 part last names like Van de Graff or the like. The proper way to do this is to head the data provider in the head with a bat and get them to provide the data in the correct format of last name being it's own column.

I've built a splitter for this type of thing including many of the 3 part names, but no lookup list in the world is going to be able to resolve all the names in the world. The data must be entered correctly in some GUI somewhere.

--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)

misscrf Valued Member Points: 73 More actions · Answer 2

I found this post in trying to split a full name with first, middle and last. I came up with this solution and thought I would share, in case it can help someone else:

,CASE WHEN LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) LIKE '%.%'

THEN REPLACE(LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))),REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),'')

ELSE LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) END AS First

,ISNULL(CASE WHEN RIGHT(REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),1) = '.'

THEN REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)) END,'') AS Middle

,LTRIM(RTRIM(LEFT(@fullname, CHARINDEX(',', @fullname)-1))) AS Last

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

misscrf (2/3/2016)
I found this post in trying to split a full name with first, middle and last. I came up with this solution and thought I would share, in case it can help someone else:
,CASE WHEN LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) LIKE '%.%'
THEN REPLACE(LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))),REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),'')
ELSE LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) END AS First
,ISNULL(CASE WHEN RIGHT(REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),1) = '.'
THEN REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)) END,'') AS Middle
,LTRIM(RTRIM(LEFT(@fullname, CHARINDEX(',', @fullname)-1))) AS Last

You should warn that it only works with the following format:

LastName, FirstName MiddleInitial.

If the comma is missing, it might generate errors. If the point for the middle initial is missing, it will give incorrect information. So this is highly dependent on correct format of the name which might be useless in many cases.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 4

Jeff Moden (7/6/2009)
Sergiy (7/5/2009)
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".
Sergiy is actually the most correct on this. Full name parsing is dangerous at best especially considering that you can have 3 part last names like Van de Graff or the like. The proper way to do this is to head the data provider in the head with a bat and get them to provide the data in the correct format of last name being it's own column.
I've built a splitter for this type of thing including many of the 3 part names, but no lookup list in the world is going to be able to resolve all the names in the world. The data must be entered correctly in some GUI somewhere.

It works only for a specific style (format) of name. That's the problem with trying to split "free text" columns, especially names.

--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)