How to split finame, lname mname

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

I have to separete this data

KIM, SUSAN H

but it is not seperate in form of lastname, firstname & middle

What I have tried:

i have use charindex() & substring() in sql

Posted 1-Aug-18 7:04am

Member 11776570

Updated 1-Aug-18 8:07am

Add a Solution

Comments

ZurdoDev 1-Aug-18 13:12pm

Yes, you need to use CHARINDEX or STRING_SPLIT() or some other text function.

Member 11776570 2-Aug-18 5:31am

But how i have used it but one name has onlu lastname & firstname.

ZurdoDev 2-Aug-18 8:03am

Then possibly an IF statement.

You have to look at all the combinations of the data and then start writing rules out in English and then translate that into SQL.

Eric Lynch 1-Aug-18 13:28pm

Be careful though, any technique will be culture-dependent. Also, even in cultures that have a clear first, middle, last, you need to consider the odd ball cases. People with no middle name and people with multiple middle names. Also, there are the occasional truly exceptional single-name cases (e.g. Cher).

Richard Deeming 3-Aug-18 15:00pm

Falsehoods Programmers Believe About Names[^]

Eric Lynch 5-Aug-18 9:55am

Great list! Although, I think I'll continue to cheat on the following assumption :) 11. People’s names are all mapped in Unicode code points.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-08-01T08:07:00

That is a presentation layer problem, and as has been mentioned is complicated by cultural issues as well as exceptions. For example, one gentleman in the UK changed his surname to "The Yorkshire Bank pLC are fascist b*st*ards" - and that is his legal name used on all official documents. There isn't a system in the world which can differentiate that from "Anna Bertha Cecilia Diana Emily Fanny Gertrude Hypatia Inez Jane Kate Louise Maud Nora Ophelia Prudence Quince Rebecca Sarah Teresa Ulysis Venus Winifred Xenophon Yetty Zeno Pepper" who was born in 1883. Then there are people like me who have no middle names at all.

The only sensible way to do this is to get the information from the user in separate text boxes, and store them in separate columns: post processing this will be horribly complex in SQL (which has very basic string manipulation features at best) and will fail in a huge number of cases.