Need help on TSQL query

Question

Dear experts,

create table Table1
(
Col1 varchar(255)
)

insert into Table1 values ('+123[00-10]')
insert into Table1 values ('+123[77-110]')

select * from Table1

Col1

+123[00-10]
+123[77-110]

Using the input from Table1, below output is required,

+12300
+12301
+12302
+12303
+12304
+12305
+12306
+12307
+12308
+12309
+12310
+12377
+12378
+12379
+12380
+12381
.
.
.
+123108
+123109
+123110

It will be of great help if I can get some help.

Regards;
Zulu

Answer 1

Hi @Zuluman Ross ,

Please refer below:

;with cte as (  
select left(Col1,CHARINDEX('[',col1)-1) col1  
,SUBSTRING(col1,CHARINDEX('[',col1)+1,CHARINDEX('-',col1)-CHARINDEX('[',col1)-1) col2  
,SUBSTRING(col1,CHARINDEX('-',col1)+1,CHARINDEX(']',col1)-CHARINDEX('-',col1)-1) col3  
from table1)  
,cte1 as (  
select distinct a.col1,a.col2,b.number  
from cte a   
cross apply  master..[spt_values] b  
WHERE number BETWEEN a.col2 AND a.col3)  
SELECT  col1+   
case when len(col2)>len(number) then '0'+ cast(number as varchar(255))  
	 else cast(number as varchar(255))  
	 end col1  
from cte1  
order by number   

Output:

col1  
+12300  
+12301  
+12302  
+12303  
+12304  
+12305  
+12306  
+12307  
+12308  
+12309  
+12310  
+12377  
+12378  
+12379  
+12380  
+12381  
+12382  
+12383  
+12384  
+12385  
+12386  
+12387  
+12388  
+12389  
+12390  
+12391  
+12392  
+12393  
+12394  
+12395  
+12396  
+12397  
+12398  
+12399  
+123100  
+123101  
+123102  
+123103  
+123104  
+123105  
+123106  
+123107  
+123108  
+123109  
+123110  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hello @MelissaMa-msft ,

Many thanks for your help.

This is what I want. Thanks again.

Regards;
Zulu