How to separate the full name to first name, last name and middle name in SQL SERVER?

Question

I have a column like [L,John,1,Paul,I,T,S,Jr.P,Mr.,,] which means [L,(last name),1,(first name),I,(middle name),S,(suffix),P,(prefix),,], Now I want to separate this column to five different columns: first name, last name, middle name, suffix, prefix with SQL Server, can anybody tell me how should I do it?

Answer 1

That should be handled prior to it getting to SQL Server. That's what SSIS packages are for.

Answer 2

I am thinking an inner select and a string split...

first, create your target table.

then,

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_mid

I cannot find a good string-split function in mysql for this, but PHP runs command-line, and you should be able to do this that way, or use perl.

in php, it would look like:

http://jesusnjim.com/web-design/php/csv-to-table-columns.html



will warn you it's going to be slow if you have big tables. if you have big tables eliminate all echo statements except maybe "done".



you have 12 columns there, not 5.

Answer 3

That table doesn't even fit 1NF. Whoever designed it made Dr. Codd cry.