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.
| Author |
Topic |
|
jayram
Starting Member
47 Posts |
 Posted - 2013-03-25 : 15:16:17
|
| hi i have my data in a table with 2 columns. The 2 columns are like below. The table is based on an excel source.e.g. Code1 Code211008 10180, 11004 – 11006i would like to select like below, Code2 has both ranges and also comma separated Should be likeCode1 Code211008 1018011008 1100411008 1100511008 11006do you think it is possible in SQL? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
jayram
Starting Member
47 Posts |
Posted - 2013-03-27 : 10:14:53
|
| Thak you Visakh. i am using the UDF parsevalue and tested on the in the example and it worksdeclare @test table(CODE1 varchar(5),CODE2 varchar(8000))insert into @test select '20936', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'insert into @test select '20937', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'select * from @testthe above is my table and i was hoping to extract like belowselect '20936', '22319'select '20936', '22532'select '20936', '22533'select '20936', '22548'select '20936', '22549'select '20936', '22550'select '20936', '22551'select '20936', '22552'......can the UDF be modified and used to get to the result i want to?Thanks |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-28 : 05:45:33
|
| [code]declare @test table(CODE1 varchar(5),CODE2 varchar(8000))insert into @test select '20936', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'insert into @test select '20937', '22319, 22532, 22533, 22548-22558, 22590-22612, 22630, 22633, 22634, 22800-22812'SELECT p.CODe1,p.StartVal + v.number AS CODE2FROM(SELECT t.CODE1,LEFT(f.Val,CHARINDEX('-',f.Val + '-')-1)*1 AS StartVal,STUFF(f.Val,1,CHARINDEX('-',f.Val + '-'),'0')*1 AS EndValFROM @test tCROSS APPLY dbo.ParseValues(t.CODE2,',') f)pCROSS JOIN master..spt_values vWHERE v.number BETWEEN 0 AND COALESCE(NULLIF(EndVal,0),StartVal)-StartValAND v.type='p'output------------------------------CODE1 CODE220936 2231920936 2253220936 2253320936 2254820936 2254920936 2255020936 2255120936 2255220936 2255320936 2255420936 2255520936 2255620936 2255720936 2255820936 2259020936 2259120936 2259220936 2259320936 2259420936 2259520936 2259620936 2259720936 2259820936 2259920936 2260020936 2260120936 2260220936 2260320936 2260420936 2260520936 2260620936 2260720936 2260820936 2260920936 2261020936 2261120936 2261220936 2263020936 2263320936 2263420936 2280020936 2280120936 2280220936 2280320936 2280420936 2280520936 2280620936 2280720936 2280820936 2280920936 2281020936 2281120936 2281220937 2231920937 2253220937 2253320937 2254820937 2254920937 2255020937 2255120937 2255220937 2255320937 2255420937 2255520937 2255620937 2255720937 2255820937 2259020937 2259120937 2259220937 2259320937 2259420937 2259520937 2259620937 2259720937 2259820937 2259920937 2260020937 2260120937 2260220937 2260320937 2260420937 2260520937 2260620937 2260720937 2260820937 2260920937 2261020937 2261120937 2261220937 2263020937 2263320937 2263420937 2280020937 2280120937 2280220937 2280320937 2280420937 2280520937 2280620937 2280720937 2280820937 2280920937 2281020937 2281120937 22812[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
jayram
Starting Member
47 Posts |
Posted - 2013-04-05 : 13:01:32
|
| Thank you visakh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-09 : 01:17:52
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
Breaking down ranges