Separate one column data into 3 columns [Resolved]

Posted by Sriharim under Sql Server on 5/19/2015 | Points: 10 | Views : 297 | Status : [Member] | Replies : 5
Hi,

I have Table like

ID     Name  
1 Kiran Kumar P
2 Mahesh C
3 Pratap singh R
4 Srihari janardhan


Required output is


ID First_name Middle_name Last_name
1 Kiran Kumar P
2 Mahesh C
3 Pratap Singh R
4 Srihari janardhan


Please give query to get required output

---
Srihari



Responses

Posted by: Bandi on: 5/19/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

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

Posted by: Sriharim on: 5/19/2015 [Member] Starter | Points: 25

Up
0
Down
very much thankful to you

---
Srihari

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

Posted by: Bandi on: 5/19/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Posted by: Sriharim on: 5/19/2015 [Member] Starter | Points: 25

Up
0
Down
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 ?

---
Srihari

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

Posted by: Bandi on: 5/19/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
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

Login to post response