Generate a number list from a string value

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?

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

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

gjack0519
Starting Member

7 Posts

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

Thanks,

I'll give that a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources