Separate one column data into 3 columns [Resolved]

declare @tab table(ID     int, Name  varchar(100))
insert @tab
SELECT 1, 'Kiran Kumar P' union all
SELECT 2, 'Mahesh C' union all
SELECT 3, 'Pratap singh R' union all
SELECT 4, 'Srihari janardhan' union all
SELECT 5, 'Mahesh' 

SELECT ID, Name, LEFT(Name,ISNULL(NULLIF(CHARINDEX(' ',Name), 0), LEN(Name))) FirstName
	, CASE LEN(Name)-LEN(REPLACE(Name, ' ', ''))
		WHEN 2 THEN SUBSTRING(Name, CHARINDEX(' ',Name), CHARINDEX(' ',Name, CHARINDEX(' ',Name))) 
		ELSE '' END	MiddleName
	,RIGHT(Name,CHARINDEX(' ',REVERSE(Name))) LastName
FROM @tab
 
/* output is 
ID   First_name   Middle_name   Last_name
1    Kiran           Kumar       P
2    Mahesh                      C
3    Pratap          Singh       R
4    Srihari                     janardhan
5	 Mahesh		
*/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

very much thankful to you

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

you are welcome :)

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

http://www.dotnetfunda.com/forums/show/19702/natural-key-and-primary-key

please clarify on below

A retail businesses (example: Relaince, payback cards) will store the customer details in a table and table has columns CustomerID, Customername, city,state,mobille number. Here, customer id is unique and can't be same to more than one person.

Whenever, i do shopping , i will give my mobile number or CustomerID and they will updated the points to my CustomerID .
Here, CustomerID exists in real world ,

Can i say CustomerID is natural key ?

order ID and product ID: In product table product ID and order table has order ID are unique, order ID and product ID are not natural key, Right ?

Like same, bank account number will be unique.
Bank account number also natural key and business key ?

Mark as Answer if its helpful to you
---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

CustomerID of Customer table, ProductId of Products table, OrderId of Orders table are primary keys, but not natural keys as they can be auto generated numbers in their tables..

Account number of Customer table is natural key/business key because it is real time data ; its part of a table ; and the table is already having a primary key or auto generated value

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator