Reputation: 2516
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
Upvotes: 0
Views: 707
Reputation: 4171
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
Upvotes: 2
Reputation: 2438
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
Upvotes: -1
Reputation: 1269563
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.
Upvotes: 0