Split Roles T-SQL Sample


  
 
create table t1
(name varchar(10),
roles varchar(100));

insert into t1 values('ABC','role1.role2.role3');
insert into t1 values('xyz','role3.role2.role4');

--===== Create number table on-the-fly

--you can use an auxiliary NUMBER table in your database to simplify your query
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

select name, substring(roles, n, charindex('.', roles + '.', n) - n)  splitRole from t1 
 
cross apply Nums
Where n <= len(roles) AND substring('.' + roles, n, 1) = '.'



--option 2
select name, role from t1 
cross apply(Values (parsename(roles,1)),(parsename(roles,2)),(parsename(roles,3)),(parsename(roles,4)))  d(role)
WHERE role is not null


----Option 3 new function will come in SQL Server 2016
----SQL Server 2016 STRING_SPLIT

--select name, value as role from t1 
--cross apply    STRING_SPLIT(roles,'.')  

drop table t1




---***********************
--************** Another
set statistics time on
set statistics io on

DECLARE @Reference TABLE
 (
 RefID int, 
 ID nvarchar(100)
 )


INSERT INTO @Reference
 SELECT 1, '422,222'
 INSERT INTO @Reference
 SELECT 2, '33,333'
 INSERT INTO @Reference
 SELECT 3, '45,63'

 --SELECT * FROM @Reference


--Option 1

--you can use an auxiliary NUMBER table in your database to simplify your query
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
select RefID, substring(ID, n, charindex(',', ID + ',', n) - n)  splitIDs from @Reference 
  
cross apply Nums
Where n <= len(ID) AND substring(',' + ID, n, 1) = ','


--Option 2
 select RefID, IDs from @Reference 
cross apply(Values (parsename(Replace(ID,',','.'),1)),(parsename(Replace(ID,',','.'),2)),(parsename(Replace(ID,',','.'),3)),(parsename(Replace(ID,',','.'),4)))  d(IDs)
WHERE IDs is not null

--Option 3

;WITH cte AS (
SELECT 
 RefID, 
 CAST('<t>' + REPLACE(ID, ',', '</t><t>') + '</t>' AS XML) AS p
FROM @Reference

)
SELECT 
  RefID, 
 x.value('.','VARCHAR(20)') AS col2
FROM cte
CROSS APPLY p.nodes('//t') a(x)

----Option 4
;with cte(RefID, ID, [level]) as
(
	select RefID, CONVERT(nvarchar(max), ID), 0
	from @Reference
	union all
	select RefID, STUFF(ID, 1, CHARINDEX(',', ID), ''), [level] + 1
	from cte
	where CHARINDEX(',', ID) > 0
)
select RefID, LEFT(ID, CHARINDEX(',', ID + ',') - 1) AS ID
from cte
order by RefID, [level]
OPTION (MAXRECURSION 0);
 


set statistics time off
set statistics io off

 

 

https://social.msdn.microsoft.com/Forums/en-US/home?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s