September 1, 2010 at 4:21 am
Hello, I have sorting problem in SQL for text data.
I have data in table like below:
================================
AA
totchip
v(389)+v(392)
Q1 Alliant Insurance Services accessibility -
Q1 Over The Past Seven Days
Q1a. Miles Driven Per Year
Q1b. First Brand To Come To Mind - Unaided
Q1_1 Optimum WiFi Awareness Summary
Q.1 Organization Type
QD1 Marital status
QSC1a Age of Children
Q2. Relationship With Aon
Q2. Which of the following broker.
Q2a. Unaided Ad Awareness
Q2-3. Would Consider - Maintenance Work
Q.2_3 Importance of Workstation Manufacturer Characteristics
QSC2 Occupation
QS2. Repair Work
Q.2b Only Server Brand
QB2b_23 Brand Imagery - Store brand - Makes me feel empowered
Q.D Decision making role
Sorting Rule : It should be sorted by letter and then by number
Please help 🙂
September 1, 2010 at 10:31 am
Hi,
This function might help - but because of the crunching it's likely to be quite inefficient so it depends how much data you have to process and how often as to whther you'll want to use this approach.
Becuase you didn't give an example of what your output should look like this is a guess.
Providing code to generate your data and an expected set of results helps people to help you, see example below.
First, create this function in your test area:
create function Make_Sort_Key (
@input varchar(100)
)
returns varchar(200)
as
begin
declare @output_collector varchar(200) = ''
-- extract the code
if charindex(' ',@input,1) <> 0
begin
set @input = left(@input,charindex(' ',@input,1)-1)
end
-- uppercase the data
set @input = upper(@input)
-- replace any junk characters
set @input = replace(@input,'.','')
set @input = replace(@input,'(','')
set @input = replace(@input,')','')
set @input = replace(@input,'+','')
set @input = replace(@input,'_','')
set @input = replace(@input,'-','')
-- use a tally table to convert the text to ascii codes
-- numbers come before letters in the ascii table
-- so this will enforce our sort order
-- note that codes in our range are all 2 chars so no need to pad
; with cte as (
select ascii(substring(@input,Tally_Number,1))as Char_Value
from Dev_Utils.dbo.Tally_Table
where Tally_Number <= len(@input)
)
select @output_collector = @output_collector + cast(Char_Value as varchar) from cte
return @output_collector
end
You'll notice that I'm referencing a Tally Table - you should also create one in your test area if you don't have one already:
create table dbo.Tally_Table(Tally_Number int)
declare @i int = 1
while @i < 101 -- just to 101 for speed on example
begin
insert dbo.Tally_Table values (@i)
set @i = @i + 1
end
-- index table
create clustered index ix_tally on dbo.Tally_Table(Tally_Number)
Now you're ready to run your test:
-- create a table variable to hold the test data
declare @Tmp table (MyText varchar(100))
-- add test data
insert @Tmp values('AA')
insert @Tmp values('totchip')
insert @Tmp values('v(389)+v(392)')
insert @Tmp values('Q1 Alliant Insurance Services accessibility -')
insert @Tmp values('Q1 Over The Past Seven Days')
insert @Tmp values('Q1a. Miles Driven Per Year')
insert @Tmp values('Q1b. First Brand To Come To Mind - Unaided')
insert @Tmp values('Q1_1 Optimum WiFi Awareness Summary')
insert @Tmp values('Q.1 Organization Type')
insert @Tmp values('QD1 Marital status')
insert @Tmp values('QSC1a Age of Children')
insert @Tmp values('Q2. Relationship With Aon')
insert @Tmp values('Q2. Which of the following broker.')
insert @Tmp values('Q2a. Unaided Ad Awareness')
insert @Tmp values('Q2-3. Would Consider - Maintenance Work')
insert @Tmp values('Q.2_3 Importance of Workstation Manufacturer Characteristics')
insert @Tmp values('QSC2 Occupation')
insert @Tmp values('QS2. Repair Work')
insert @Tmp values('Q.2b Only Server Brand')
insert @Tmp values('QB2b_23 Brand Imagery - Store brand - Makes me feel empowered')
insert @Tmp values('Q.D Decision making role')
--
select *, dbo.Make_Sort_Key(MyText)
from @Tmp
order by dbo.Make_Sort_Key(MyText)
Is this the right order?
Regards, Iain
September 1, 2010 at 11:00 am
When you say "sorted by letter then number", it would be helpful if you'd give an example of what you want vs what you get.
If, for example, you want:
Q1 Alliant Insurance Services accessibility -
Q1 Over The Past Seven Days
Q1a. Miles Driven Per Year
Q1b. First Brand To Come To Mind - Unaided
Q1_1 Optimum WiFi Awareness Summary
Q.1 Organization Type
vs
Q.1 Organization Type
Q1 Alliant Insurance Services accessibility -
Q1 Over The Past Seven Days
Q1_1 Optimum WiFi Awareness Summary
Q1a. Miles Driven Per Year
Q1b. First Brand To Come To Mind - Unaided
That will make a difference in how it's sorted.
Does the rule mean sort by the first "code" entry? I.e.: Up to the first space?
If you want the first one, instead of the second one, try something like this:
declare @T table (
C1 varchar(1000));
insert into @T (C1)
values ('Q1 Alliant Insurance Services accessibility -'),
('Q1 Over The Past Seven Days'),
('Q1a. Miles Driven Per Year'),
('Q1b. First Brand To Come To Mind - Unaided'),
('Q1_1 Optimum WiFi Awareness Summary'),
('Q.1 Organization Type');
;with
CTE1 as
(select LEFT(C1, charindex(' ', C1)) as Code, C1, ROW_NUMBER() over (order by C1) as Row
from @T),
CTE2 as
(select SUBSTRING(Code, Number, 1) as Sub, Row, Number
from CTE1
inner join Common.dbo.Numbers
on Number <= LEN(Code)),
CTE3 as
(select distinct Row,
(select
case
when 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' like '%' + Sub + '%' then '0'
when '0123456789' like '%' + Sub + '%' then '1'
else '2'
end + Sub
from CTE2 CTE2a
where Row = CTE2.Row
for XML path('')) as Seq
from CTE2)
select C1
from CTE1
inner join CTE3
on CTE1.Row = CTE3.Row
order by Seq;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply