Split column values into two columns in Result set

Question

I have employee table with single column but the data in it contains emp name and emp id seperated by '.' I want a select statement to separate name and id , but the catch is some names have '.' between the name value itself. below is the table and values

Create table #employee
(
empdetails varchar(max)
)

insert into #employee values('hill.1')
insert into #employee values('Jim.kim.2')
insert into #employee values('sam.3')
insert into #employee values('vic.gin.4')

output should be

empname empid
hill 1
Jim.kim 2
sam 3
vic.gin 4

Answer 1

Hi @Rahul Polaboina ,

Try this query:

Create table #employee  
(  
empdetails varchar(max)  
)  
  
insert into #employee values('hill.1')  
insert into #employee values('Jim.kim.2')  
insert into #employee values('sam.3')  
insert into #employee values('vic.gin.4')  
  
select * from #employee  
  
select left(empdetails,len(empdetails)-charindex('.',REVERSE(empdetails))) as empname   
		,left(REVERSE(empdetails),CHARINDEX('.',REVERSE(empdetails))-1) as empid  
from #employee  

Best regards,
Niko

----------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Rahul Polaboina ,

Please try the following solution.
It is based on tokenization via XML and XQuery.

Notable points:

1. CROSS APPLY is tokenizing input as XML.
2. XPath predicate [last()] gives the last token.
3. XPath predicate [position() lt last()] gives all tokens except the last.

SQL

-- DDL and sample data population, start  
DECLARE @tbl table  (ID INT IDENTITY PRIMARY KEY, empdetails VARCHAR(100));  
INSERT @tbl (empdetails) VALUES   
('hill.1'),  
('Jim.kim.2'),  
('sam.3'),  
('vic.gin.4');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(1) = '.';  
  
SELECT t.*   
 , REPLACE(c.query('data(/root/r[position() lt last()]/text())').value('.', 'VARCHAR(100)'), SPACE(1), @separator) AS empname    
 , c.value('(/root/r[last()]/text())[1]', 'INT') AS empid    
FROM @tbl AS t  
CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' +   
      REPLACE(empdetails, @separator, ']]></r><r><![CDATA[') +   
      ']]></r></root>' AS XML)) AS t1(c);  

Output

+----+------------+---------+-------+  
| ID | empdetails | empname | empid |  
+----+------------+---------+-------+  
|  1 | hill.1     | hill    |     1 |  
|  2 | Jim.kim.2  | Jim.kim |     2 |  
|  3 | sam.3      | sam     |     3 |  
|  4 | vic.gin.4  | vic.gin |     4 |  
+----+------------+---------+-------+  

Answer 3

Create table #employee  
(  
empdetails varchar(max)  
)  
  
insert into #employee values('hill.1')  
insert into #employee values('Jim.kim.2')  
insert into #employee values('sam.3')  
insert into #employee values('vic.gin.4')  
   
  
 select *   
 ,concat_ws('.',  
 parsename(empdetails,4),  
 parsename(empdetails,3),  
 parsename(empdetails,2)) empname,  
  
 parsename(empdetails,1)   empid    
  
 from  #employee  
  
 drop table #employee  

Answer 4

Here is a solution that works with other delimiter than '.'. And which works also if there are more than three delimiters in the string.

   SELECT substring(empdetails, 1, len(empdetails) - charindex('.', reverse(empdetails))),  
          try_convert(int, right(empdetails, charindex('.', reverse(empdetails)) - 1))  
   FROM   #employee