April 30, 2014 at 1:33 am
create table #temp
(
range2 varchar(15),
descrip varchar(10)
)
insert into #temp
select '10-13','test one'
union
select 'T100-T1105','test two'
union
select '20G-22G','test three'
select * from #temp
output required
create table #temp1
(
range2 varchar(15),
descrip varchar(10)
)
insert into #temp1
select '10','test one'
union
select '11','test one'
union
select '12','test one'
union
select '13','test one'
union
select 'T100','test two'
union
select 'T101','test two'
union
select 'T102','test two'
union
select 'T103','test two'
union
select 'T104','test two'
union
select 'T105','test two'
union
select '20G','test three'
union
select '21G','test three'
union
select '22G','test three'
select * from #temp1
input
select * from #temp
output required
select * from #temp1
April 30, 2014 at 2:31 am
;WITH cte (prefix,suffix,leftpart,rightpart,descrip) AS (
SELECT CASE WHEN LEFT(range2,1) LIKE '[a-zA-Z]' THEN LEFT(range2,1) ELSE '' END,
CASE WHEN RIGHT(range2,1) LIKE '[a-zA-Z]' THEN RIGHT(range2,1) ELSE '' END,
LEFT(range2,CHARINDEX('-',range2)-1),
SUBSTRING(range2,CHARINDEX('-',range2)+1,255),
descrip
FROM #temp),
cte2 (prefix,suffix,startno,endno,descrip) AS (
SELECT prefix,suffix,
CAST(REPLACE(REPLACE(leftpart,prefix,''),suffix,'') as int),
CAST(REPLACE(REPLACE(rightpart,prefix,''),suffix,'') as int),
descrip
FROM cte)
SELECT prefix+CAST(startno+N as varchar(10))+suffix,descrip
FROM cte2
JOIN master.dbo.Tally t ON t.N BETWEEN 0 AND endno-startno
Far away is close at hand in the images of elsewhere.
Anon.
April 30, 2014 at 2:37 pm
Hi
Here's my attempt at it. I am making some assumptions about your data that you will need to take into account.
1. There will only be up to a single letter at either the end or the beginning
2. That letter will be the same on both sides of the hyphen
3. The number range will always be low to high
It will also work with range values like '31J' and 'S2-S2'
WITH parseRange AS (
SELECT num1 = cast(replace(rng1,letter,'') as int),
num2 = cast(replace(rng2,letter,'') as int),
prefix = case when ltrsuffix=0 then letter else '' end,
suffix = case when ltrsuffix=1 then letter else '' end,
descrip
FROM (
SELECT rng1 = left(range2, len(range2) - charindex('-',reverse(range2)))
,rng2 = right(range2, len(range2) - charindex('-',range2))
,letter = substring(range2,patindex('%[^0-9-]%', range2),1)
,ltrsuffix = cast(patindex('%[^0-9-]%', range2) - 1 as bit)
,descrip
from #temp
) r
),
CTETally AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e1(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e2(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e3(N),
(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e4(N)
)
SELECT prefix + CAST(N + num1 - 1 AS VARCHAR(10)) + suffix, descrip
FROM parseRange p
CROSS APPLY (SELECT TOP (num2 - num1 + 1) N FROM CTETally) t;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply