RE: Sorting 2d arrays

[Dave Phillips]

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)

cfquery datasource=whatever name=qMySuggestions
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
/cfquery

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:

cfquery name=qFindMatch dbtype=query
SELECT sql_id FROM qMySuggestions
WHERE (put your condition here to find a match)
/cfquery

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

Re: Sorting 2d arrays

[Tony Bentley]

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 Bentley]

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