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
