please help with string function

Question

Post reply

please help with string function

nicolamariana

SSC Journeyman

Points: 86
More actions
May 28, 2008 at 12:56 pm

#186153

Hi ALL,
Please I need your help. Your time is very much appreciated.
I have the following table:
A
name
Pitt, Lewis ---- last name before comma
Sharon Stone ---- first name and last name
I need to create the table:
A
actorid firstname lastname
178 Lewis Pitt
179 Sharon Stone
Thank you very much,
Mary

Viewing 8 posts - 1 through 7 (of 7 total)

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

Chad Crawford SSChampion Points: 11610 More actions · Answer 1

Mary,

Can you post some of the ideas you have tried?

Thanks,

Chad

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

This seems like homework, please ask the OP to make an attempt before you give the answer here.

nicolamariana SSC Journeyman Points: 86 More actions · Answer 3

Hi guys,

Thanks a lot for your prompt replay and trying to really help me.

select actorname,

substring(actorname, 1, (C-1))

as lastname,

substring (actorname,(C+1),L)

as firstname

from A

select actorname,

substring(actorname, 1, (S-1))

as firstname,

substring (actorname,(S+1),L)

as lastname

from A

C is charindex(', ',ActorName)

S is charindex(' ',ActorName)

L is length(ActorName)

I was wondering if there is a function which counts the number of spaces in the string. They might have a name like John George Stone.

Thank you VERY MUCH for your time.

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 4

You can get the number of spaces by...

Len(actorname) - Len(replace(actorname,' ',''))

If you are just looking for the last name in a three part name you can use the Reverse function to reverse the string before doing the charindex.

Len(actorname) - Charindex(' ',Reverse(actorname)) + 1

This will give you the starting position of the last name.

Vladan SSC-Insane Points: 22072 More actions · Answer 5

You can never write a query that will take care of everything and split names or addresses 100% correctly... so your task should be to write a query that will take care of MOST cases, while marking the rest in a way that the operator knows they have to be checked and converted manually.

There are names like J. R. R. Tolkien, Robin van Persie (where "van Persie" is family name), Peter van der Linden (where "van der Linden" is family name)... there could be names that contain title, like Prof. Albert Einstein (where do you put the Prof.? into family name, first name, or into a special column?). And, there is of course always the possibility of typos in the name, e.g. two spaces between the names instead of one.

Of course, if this is a homework or learning project, and you are required to write the query for a given (limited) set of values, then you could be able to write a query which will convert everything - but not in a real life problem, especially if you want to write it so that it can be re-used with any data, not just the data you have at the moment.

nicolamariana SSC Journeyman Points: 86 More actions · Answer 6

yes, indeed there are all kinds of names.

Thanks a lot.

Chad Crawford SSChampion Points: 11610 More actions · Answer 7

You could combine both of your queries into one using a CASE. Make it even more powerful using Ken's REVERSE and then manually inspect the oddball ones to make sure they are correct. Like Vladan pointed out, knowing that there are two spaces in the name still doesn't tell you how to split it, but it does flag a record that needs to be inspected.

SELECT CASE WHEN CHARINDEX(',', @Name) > 0

THEN SUBSTRING(@Name, CHARINDEX(',', @Name) + 2, LEN(@Name)) --+2, one for the comma, one for the space

ELSE LEFT(@Name, CHARINDEX(' ', @Name)) END AS FirstName

, CASE WHEN CHARINDEX(',', @Name) > 0

THEN LEFT(@Name, CHARINDEX(',', @Name) - 1)

ELSE SUBSTRING(@Name, CHARINDEX(' ', @Name) + 1, LEN(@Name)) END AS LastName

Change the @Name variable to your column name

Hope this helps,

Chad