Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Generate a number list from a string value

Author  Topic 

gjack0519
Starting Member

7 Posts

Posted - 2013-10-22 : 16:28:51
I have a single column that has string values like ['61000-61055']. I am trying to get the list of numbers that would start with 61000, and end with 61055. The actual data has the beginning and ending quotes as part of the string. My output would have a number on each row.
61000
61001
61002...

Using SQL Server 2008 R2

Thanks,
gjack

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-22 : 16:52:38
Are all the rows in the table consistently of the same pattern - i.e., a left square bracket, followed by a single quote, followed by a number, followed by a hyphen, followed by a number then single quote and square bracket?
Go to Top of Page

gjack0519
Starting Member

7 Posts

Posted - 2013-10-22 : 18:39:52
They are all consistent with a single quote, number, hyphen, number, then single quote'
exp. '61000-61055'

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-23 : 02:07:51
[code]
SELECT StartVal + v.number
FROM
(
SELECT REPLACE(LEFT(Col,CHARINDEX('-',Col)-1),'[''','') AS StartVal,
REPLACE(STUFF(Col,1,CHARINDEX('-',Col),''),''']','') AS EndVal
FROM table
)t
CROSS JOIN master..spt_values v
WHERE v.type = 'p'
AND StartVal + v.number <= EndVal
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

gjack0519
Starting Member

7 Posts

Posted - 2013-10-23 : 16:42:40
Thanks,

I'll give that a try.
Go to Top of Page
   

- Advertisement -