asked on

TSQL/SQL/Access - Splitting the Full Name from the surname

Hi Experts,

I would like to split or remove the Mr., Ms., Ms, Mr, from the front of the FullName?
I have this in the FullName column:
Mr. John Smith
Ms. Jone Smith
Ms John Smith

I would like:
John Smith
Jone Smith
John Smith

In the FullName column

I have this
Select
LTRIM(RTRIM(SUBSTRING(FullName, 0, CHARINDEX(' ', FullName)))) As Surname
FROM
[table]

but all that does is gives me another column with Mr. Ms.
and does not take out the Mr. from FullName

I would like either/or/both in Access query or/both TSQL query

Please help and thanks...

Vikas Garg

Select  SUBSTRING(FULLNAME,CHARINDEX(' ', @FULLNAME)+1,LEN(FULLNAME)-CHARINDEX(' ', FULLNAME)) FROM tABLE

Open in new window

Amour22015

ASKER

Hi,

That is looking for a parameter @FullName?

So what do I declare the parameter as?
also will this replace it in the FullName column?

Thanks

SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Kevin Cross,

There is no way of knowing the Mr., Ms.,Mrs., Mis., or even if there is a title in front of the name so matching will not work in this case?

Also what if Mr.John? without any space that was put incorrectly?

So this is not very easy. The good news is that I found out that this Mr. John Smith is ok to have in the column and does not need changing.

so this post has turned into a information post for others to see.

Thanks for helping.

PatHartman

Hopefully the thread will stand of an example of why fields should be atomic. Nothing is gained by mushing name parts into a single column and much is lost.

ASKER CERTIFIED SOLUTION

Vikas Garg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Great thanks