Re: Sorting 2d arrays
Sorry, forgot to mention that the query above just returns the ASCII chars. Then I use a different query to classify the first char: DECLARE @ErrorNum INT SET @ErrorNum = 1 DECLARE @ TABLE ( [Id]INT, [Name] VARCHAR(255), [Number]VARCHAR(255), [_] VARCHAR(255), [_] VARCHAR(255), [SortOrder] TINYINT) ERT INTO@ SELECT a.[Id], a.[Name], CASE WHEN ISNULL(a.[Number], '') = '' THEN '' ELSE a.[Number] END, CASE WHEN ISNULL(a.[Number], '') = '' THEN a.[Name] ELSE a.[Name] + ' -- ' + a.[Number] END, CASE WHEN ISNULL(a.[Number], '') = '' THEN '' ELSE a.[Number] + ' -- ' + a.[Name] END, CASE WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 0 AND 47 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 48 AND 57 THEN 2 -- numbers ordered second WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 58 AND 64 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 65 AND 90 THEN 3 -- letters ordered third WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 91 AND 96 THEN 1 -- symbols ordered first WHEN ASCII(LEFT(a.[Name], 1)) BETWEEN 97 AND 122 THEN 3 -- letters ordered third ELSE 1 END FROM[dbo].[] AS a WHERE a.[Name] <> 'All s' There is more but you get the idea. Talk about rabbit and tortoise! ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327264 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Sorting 2d arrays
Okay I figured it was going to be a query based solution. Turns out there is a table in SQL Server that lists all chars in ASCII so I can sort based on that table's numeric values and then return two columns to output. Pretty RAD. here's the t-sql to generate it. --- set nocount on declare @integers table ([Number] INT) declare @i int declare @char varchar(255) set @i = 1 while @i < 128 begin insert into @integers values (@i) set @i = @i + 1 end declare char_test cursor for select [Number] from @integers open char_test fetch next from char_test into @char while @@fetch_status = 0 begin print 'char #' + @char + ' = ' + char(@char) fetch next from char_test into @char end close char_test deallocate char_test ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327263 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Sorting 2d arrays
Tony, Probably a ton of ways to do this. Would this get you what you want?: (note: the below would be for oracle, you need + instead of || for Sql server - not sure about what db you're using) select sql_id, alphanumeric_id || description as auto_suggest_descr from mytable union select sql_id, description || alphanumeric_id as auto_suggest_descr from mytable order by augo_suggest_descr Now you should have a query with the results in the order you want them with the original sql_id also there. Now instead of looping through an array to do a match, you simply do a query of query: SELECT sql_id FROM qMySuggestions WHERE (put your condition here to find a match) qFindMatch.sql_id is the record you want. Dave Phillips ~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:327252 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4