Extracting values from a string into multiple columns

Question

I have a table called Types and the data is like:

Item_Name 

Hardware \ Hardware Laptop \ Display
Application \ Application Configuration Request
Application \ Application File Request
Application

I am trying to split them into 3 different columns. So tried doing it by the following query:

select parsename(replace([Item_Name],'\','.'),3) as First,
 parsename(replace([Item_Name],'\','.'),2) as Second,
 parsename(replace([Item_Name],'\','.'),1) as third
from dbo.Types

But i am not getting the exact result the output i want is:

First          Second                                      Third
Hardware       Hardware Laptop                             Display
Application    Application Configuration Request           NULL
Application    Application File Request                    NULL
Application    NULL                                        NULL

But I am getting output as:

First          Second                          Third
Hardware       Hardware Laptop                 Display
NULL           Application                     Application Configuration Request           
NULL           Application                     Application File Request                    
NULL           NULL                            Application

I like clever things, but I don't care for using PARSENAME this way. — GilM
– GilM, Commented Aug 30, 2012 at 22:00

Niladri Biswas · Accepted Answer · 2012-08-31 05:45:26Z

This may be useful

Declare @t table(Item_Name Varchar(100))
Insert Into @t 
Select 'Hardware \ Hardware Laptop \ Display' Union All 
Select 'Application \ Application Configuration Request' Union All
Select 'Application \ Application File Request' Union All
Select 'Application'

;WITH CTE AS(
SELECT 
    Item_Name
    ,NoOfOccurance = (LEN(Item_Name) - LEN(REPLACE(Item_Name, '\', ''))) 
    ,New_Item_Name = CASE (LEN(Item_Name) - LEN(REPLACE(Item_Name, '\', ''))) 
                         WHEN 0 THEN 'Col1 \ Col2 \ ' +Item_Name
                         WHEN 1 THEN 'Col1 \ ' +Item_Name 
                         ELSE Item_Name
                     END
FROM @t)
,cte2 AS(
SELECT 
    [First] = LEFT(New_Item_Name,CHARINDEX('\',New_Item_Name,0)-1)  
    , [Second] = SUBSTRING(
                 New_Item_Name
                 ,CHARINDEX('\',New_Item_Name,0)+1
                 ,CHARINDEX('\',SUBSTRING(New_Item_Name,CHARINDEX('\',New_Item_Name,0)+1,len(New_Item_Name)),0)-1
               )
    ,[Third] = REVERSE(LEFT(REVERSE (New_Item_Name),CHARINDEX('\',REVERSE (New_Item_Name),0)-1))

FROM CTE)
SELECT
    [First] = CASE WHEN LTRIM(RTRIM([First])) = 'Col1' THEN REPLACE([First], 'Col1', NULL) ELSE [First] END
    ,[Second] = CASE WHEN LTRIM(RTRIM([Second])) = 'Col2' THEN REPLACE([Second], 'Col2', NULL) ELSE [Second] END 
    ,[Third]    
FROM Cte2

//Result

First   Second  Third
Hardware     Hardware Laptop     Display
NULL     Application     Application Configuration Request
NULL     Application     Application File Request
NULL    NULL     Application

Gordon Linoff · Accepted Answer · 2012-08-30 21:23:09Z

I consider this brute force, but it works. The idea is to add enough slashes at the end to make a full name. This will push things over:

with types as (select 'Hardware \ Hardware Laptop \ Display' as item_name union all
               select 'Application \ Application Configuration Request' union all
               select 'Application \ Application File Request' union all
               select 'Application'
              )
select parsename(replace([Item_Name]+suffix, '\', '.'), 3) as First,
       parsename(replace([Item_Name]+suffix, '\', '.'), 2) as Second,
       parsename(replace([Item_Name]+suffix, '\', '.'), 1) as third
from (select item_name, LEN(Item_name) - LEN(replace(item_name, '\', '')) as numslash,
             REPLICATE('. ', 2-(LEN(Item_name) - LEN(replace(item_name, '\', '')))) as suffix
      from Types
     ) t

One slight difference is that this produces spaces rather than NULLs for the empty slots. If you want NULLs, you'll need additional logic.

Ruzbeh Irani · Accepted Answer · 2012-08-30 21:23:27Z

-1

WITH CTE AS(
 SELECT CASE LEN(Item_Name)-LEN(REPLACE(Item_Name,'\','')) WHEN 2 THEN [Item_Name] WHEN 1 THEN  
         [Item_Name]+'.' Else [Item_Name]+'..' as Item_Name
 FROM dbo.Types 
 )
SELECT parsename(Item_Name,'\','.'),3) as First,
       parsename(Item_Name,'\','.'),2) as Second,
       parsename(Item_Name,'\','.'),1) as third 
FROM CTE

answered Aug 30, 2012 at 21:23

Ruzbeh Irani

2,4561 gold badge19 silver badges10 bronze badges

1 Comment

GilM Over a year ago

Lots of errors with this (missing some REPLACE(s), doesn't work without trailing value (like space in last section)...

Collectives™ on Stack Overflow

Extracting values from a string into multiple columns

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related