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 |
|
sureshot
Yak Posting Veteran
72 Posts |
 Posted - 2006-01-20 : 14:07:45
|
| I'm looking to convert an ordered list of rows coming from a query that contain a number into a condensed format with hyphened ranges.ex) select * from numberlist12357=>"1-3, 5, 7"So I'm considering either writing a UDF with TSQL or with SQL 2005 CLR to do create this string. This is a high load system so regardless of which way, I'm also strongly considering running the code batched offline to put the string in a column.Any ideas on a UDF? I'd prefer to not use CLR if I don't have to. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-20 : 14:09:05
|
| Why not return the values to the application, then have the application format the data as needed?Tara Kizeraka tduggan |
 |
|
|
sureshot
Yak Posting Veteran
72 Posts |
Posted - 2006-01-20 : 17:04:58
|
| That's what going on now and it's a second pass at the data plus extra transfer to send all the rows down when all I really care about is this string. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-20 : 17:25:33
|
| It will be inefficient to do this in the database. Formatting in the application as you are currently doing will perform better. T-SQL isn't meant for this type of operation, although it could do it. Two passes at the data is not inefficient as the first past is in the database and the second is in the application. Returning the data as the string would not benefit performance unless we are talking about a huge number of rows, such as 1-1000000 with most of the numbers being present in the list.Tara Kizeraka tduggan |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
Condensing List of Numbers