March 5, 2012 at 10:19 am
Guys,
Is it possible to sort in the following order for a column which starts special characters and alpha numeric characters.
!
"
$
?
@
^
{
>
1
2
3
4
5
6
7
8
9
A
B
C
D
Thanks
March 5, 2012 at 10:33 am
Hmmm, I'll take a stab.
In your order by clause use a case statement. Similar to this,
SELECT col1
FROM tableA
ORDER BY
CASE
WHEN col1 = '!' THEN
1
WHEN col1 = '"' THEN
2
WHEN col1 = '$' THEN
3
...
END
You might even put this in a table valued function. Have the function return the character and its corresponding sort number. Join the TVF to your result set sorted by the TVF sort field.
What do you think about that?
March 5, 2012 at 10:40 am
Just an idea:
CREATE TABLE [dbo].[MyTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[realvalue] [varchar](50) NULL,
[firstChar] AS (left([realvalue],(1))),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([id] ASC)
)
GO
CREATE TABLE dbo.SpecialCharSort (SpecialChar char(1), sortOrder int)
insert dbo.SpecialCharSort
values ('!', -10)
,('"', -9)
,('$', -8)
,('?', -7)
,('@', -6)
,('^', -5)
,('{', -4)
,('>', -3)
insert [MyTable]
values ('test')
,('Dada')
,('Caca')
,('Baba')
,('AaAa')
,('9aaa')
,('8aaa')
,('7aaa')
,('6aaa')
,('5aaa')
,('4aaa')
,('3aaa')
,('2aaa')
,('1aaa')
,('0aaa')
,('>aaa')
,('{aaa')
,('^aaa')
,('@aaa')
,('?aaa')
,('$aaa')
,('"aaa')
,('!aaa')
,('£aaa')
select * from MyTable order by [realvalue]
select t.*, s.*
from MyTable t
left join SpecialCharSort s on s.SpecialChar = t.firstChar
order by isnull(s.sortOrder, 999999), realvalue
If you can use calculated column, then you can create order control table and configure any order you like. You can see that numbers 0-9 and ABCD doesn't need to have any special order as they will be naturally sorted in the order you want...
March 5, 2012 at 10:46 am
Here is another way of doing something similar to the previous example.
create table #Chars
(
MyChar char(1)
)
insert #Chars (MyChar)
values
('!'), ('"'), ('$'), ('?'), ('@'), ('^'), ('{'), ('>'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D')
select *,
CASE MyChar WHEN '!' THEN 1
WHEN '"' THEN 2
WHEN '$' THEN 3
WHEN '?' THEN 4
WHEN '@' THEN 5
WHEN '^' THEN 6
WHEN '{' THEN 7
WHEN '>' THEN 8
else ASCII(MyChar)
end as SortOrder
from #Chars
order by
CASE MyChar WHEN '!' THEN 1
WHEN '"' THEN 2
WHEN '$' THEN 3
WHEN '?' THEN 4
WHEN '@' THEN 5
WHEN '^' THEN 6
WHEN '{' THEN 7
WHEN '>' THEN 8
else ascii(MyChar)
end
drop table #Chars
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 10:50 am
You could also add a left to the order by in my example if your real data is longer than 1 character.
order by
case left(MyChar, 1) when '!' then 1
...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 5, 2012 at 11:11 am
Simplify a step further:
create table #Chars
(
MyChar char(1)
)
insert #Chars (MyChar)
values
('!'), ('"'), ('$'), ('?'), ('@'), ('^'), ('{'), ('>'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D')
SELECT *
FROM #Chars
ORDER BY MyChar COLLATE SQL_Latin1_General_Cp1251_CS_AS
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2012 at 12:47 pm
March 5, 2012 at 2:23 pm
I busted through a ton of them (collations) until I found the right one. With this sort of "sort" requirement, it really seems like a homework assignment.
I decided to check the collations since others have been so kind to find a collation after I worked out a sort solution in the past that the collation proved a better method.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply