-- jonathan_mallia_tsqlchallenge_14.sql
/*
Data
------------------------------------
9992EDC6-D117-4DEE-B410-4E5FAE46AE97
0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1
4A73E7EB-7777-4A04-9258-F1E75097977C
5AAF477C-274D-400D-9067-035968F33B19
725DA718-30D0-44A9-B36A-89F27CDFEEDE
8083ED5A-D3B9-4694-BB04-F0B09C588888
*/
/*
-- INPUT
DECLARE @t TABLE (Data VARCHAR(40) )
INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
*/
/*
-- EXPECTED OUTPUT
Data Char Pos Len
------------------------------------ ---- ---- ----
8083ED5A-D3B9-4694-BB04-F0B09C588888 B 20 2
8083ED5A-D3B9-4694-BB04-F0B09C588888 8 32 5
4A73E7EB-7777-4A04-9258-F1E75097977C 7 10 4
4A73E7EB-7777-4A04-9258-F1E75097977C 7 34 2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 9 1 3
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 1 11 2
9992EDC6-D117-4DEE-B410-4E5FAE46AE97 E 17 2
5AAF477C-274D-400D-9067-035968F33B19 A 2 2
5AAF477C-274D-400D-9067-035968F33B19 7 6 2
5AAF477C-274D-400D-9067-035968F33B19 0 16 2
5AAF477C-274D-400D-9067-035968F33B19 3 32 2
725DA718-30D0-44A9-B36A-89F27CDFEEDE 4 15 2
725DA718-30D0-44A9-B36A-89F27CDFEEDE E 33 2
*/
-- D O N E ! ! ! !
IF OBJECT_ID('tempdb..#tt') IS NOT NULL -- Temp Table Exists
BEGIN
DROP TABLE #tt
END
IF OBJECT_ID('tempdb..#ttfinal') IS NOT NULL -- Temp Table Exists
BEGIN
DROP TABLE #ttfinal
END
IF OBJECT_ID('tempdb..#temper') IS NOT NULL -- Temp Table Exists
BEGIN
DROP TABLE #temper
END
DECLARE @t TABLE (ID INT IDENTITY(1,1), Data VARCHAR(40) )
INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
SELECT TOP 10000 IDENTITY(INT,1,1) AS RowNum into #temper from master.sys.syscolumns
;WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
Split AS (
SELECT id,
SUBSTRING('-' + data + '-', n + 1,
CHARINDEX('-', '-' + data + '-', n + 1) - n - 1) AS data,
n + 1 - LEN(REPLACE(LEFT(data, n), '-', '')) AS idx
FROM Nums
JOIN @t ON SUBSTRING('-' + data + '-', n, 1 ) = '-'
AND n < LEN('-' + data + '-')
),
y AS
(
select id, substring(data, rownum, 1) as data, idx, rownum
from #temper, split
)
select distinct id as DataStack, data, idx as SectorPart, rownum, row_number() over(partition by id order by id, idx, rownum) AS RN
into #tt
from y a
where a.data != ''
order by datastack, idx, rownum, rn
select distinct a.*,
b.data as [BEFORE], c.data AS [AFTER],
case when a.data = c.data OR a.data = b.data then 'here_is_a_match'
end as 'MatchString'
into #ttfinal
from #tt a
left join #tt b on a.rownum = b.rownum + 1 and a.datastack = b.datastack and a.sectorpart = b.sectorpart
left join #tt c on a.rownum = c.rownum - 1 and a.datastack = c.datastack and a.sectorpart = c.sectorpart
order by a.datastack, a.SectorPart, a.rownum, a.rn
select (select data from @t where ID = fi.datastack) AS Data,
data AS Char,
case when sectorpart = 1 then min(rownum)
when sectorpart = 2 then min(rownum) + 9
when sectorpart = 3 then min(rownum) + 14
when sectorpart = 4 then min(rownum) + 19
when sectorpart = 5 then min(rownum) + 24
end AS POS,
count(data) AS Length,
SectorPart
from #ttfinal fi
where MatchString = 'here_is_a_match'
group by data, datastack, SectorPart
order by datastack, POS
/*
-- testing
-- CONTINUE TESTING FROM HERE !!!!
/*
DECLARE @t TABLE (ID INT IDENTITY(1,1), Data VARCHAR(40) )
INSERT @t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'
INSERT @t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'
INSERT @t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'
INSERT @t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'
INSERT @t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'
INSERT @t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'
--SELECT TOP 10000 IDENTITY(INT,1,1) AS RowNum into #temper from master.sys.syscolumns
;WITH y AS
(
select id, substring(data, Rownum, 1) as data, RowNum
from @t, #temper
)
select distinct A.id, a.data, a.rownum, b.data as [BEFORE], c.data AS [AFTER],
case when a.data = c.data OR a.data = b.data then 'here_is_a_match'
end as 'MatchString'
into #matches
from y a
left join y b on a.rownum = b.rownum + 1 AND A.ID = B.ID
left join y c on a.rownum = c.rownum - 1 AND A.ID = C.ID
where a.data != ''
ORDER BY A.ID, A.ROWNUM
select (select data from @t where ID = fi.ID) AS Data,
data AS Char, min(rownum) AS POS, count(data) AS Length
from #matches fi
where MatchString = 'here_is_a_match'
group by data, id
order by id, Length Desc
*/
*/
