order by on nvarchar field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

order by on nvarchar field

Author

Topic

ferchen
Starting Member

5 Posts

Posted - 2009-04-16 : 07:50:17

Hi all,
thanks in advance for reading and helping.
I have a table with an nvarchar(50) filed, the data contains text and/or numbers, when I'm trying to sort the data with I get wrong order, some of the rows with text(some contain numbers) appears before the rows with numbers, then all of the rows with numbers, and then the rest of the rows with text(some contain numbers). I tried to cast it to varbinary and I think it explains the problem; The sort I get is for some reason converted to varbinary and the being sorted.
I expect a dictionary sort.
Again, thanks for your help,
Dani.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-16 : 09:08:34

You're going to have to provide examples, but SQL Server will be doing the right thing. Mixing numbers & text is going to cause these issues and you probably will have to redesign. Maybe use isnumeric() but usually this will suck too.

ferchen
Starting Member

5 Posts

Posted - 2009-04-16 : 09:40:46

Hi,
examples are very simple, as I described:
I have to metion that it's hebrew text so they are not the same exaples as below:
1. ber 356
2. bez 896
3. 730
4. 750
5. 753
6. abf
7. acg
8. df 7 e
9. dz 9
10. ert

etc.
It's like "divided" to 3 sections and every section is sorted correctly.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-16 : 10:11:09

So you are saying that your data is
Row followed by "."
Some text
space
Some more text
space
more text

When there are missing columns, how do you know which is which? e.g. row 3 - is the 730 column 2, 3 or 4?
Can you explain how you think your example should be sorted and why?

ferchen
Starting Member

5 Posts

Posted - 2009-04-16 : 12:37:04

Hi,
I expect the data to be dictionary sorted:

3. 730
4. 750
5. 753
6. abf
7. acg
1. ber 356
2. bez 896
8. df 7 e
9. dz 9
10. ert

or numbers last
thanks.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-04-17 : 03:28:25

Your example shows numbers last!
I'm confused.

maybe this will get you 'numbers last' but I'm still not sure what you're after.

ORDER BY
case when col < 'A' then 'ZZZZ'+col
else col end;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 03:45:25

Numbers last


select data from
(
	select 'test' as data union all
	select '345' union all
	select 'as345' union all
	select 'b5' union all
	select 'f5' union all
	select 'd445' union all
	select '65' 
) as t
order by case when data not like '%[^0-9]%' then data*1 else 1 end, data

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-17 : 03:56:06

[code]DECLARE @Sample TABLE
(
ID INT,
Data VARCHAR(20),
ExpectedOrder INT
)

INSERT @Sample
SELECT 3, '730', 1 UNION ALL
SELECT 4, '750', 2 UNION ALL
SELECT 5, '753', 3 UNION ALL
SELECT 6, 'abf', 4 UNION ALL
SELECT 7, 'acg', 5 UNION ALL
SELECT 1, 'ber 356', 6 UNION ALL
SELECT 2, 'bez 896', 7 UNION ALL
SELECT 8, 'df 7 e', 8 UNION ALL
SELECT 9, 'dz 9', 9 UNION ALL
SELECT 10, 'ert', 10

SELECT ID,
Data,
ExpectedOrder
FROM @Sample
ORDER BY Data[/code]

E 12°55'05.63"
N 56°04'39.26"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-17 : 08:08:08

Since the example given doesn't demonstrate the problem, and the real data has a mixture of Hebrew letters (RTL) and 'Arabic' numerals (LTR), I think the problem has more to do with order within the rows than between them.

Does this data show the sort of problem you are having? It looks to me like a plausible row order. (sorry if the Hebrew words are nonsense!)


SELECT LTRIM(RTRIM(REPLACE(prefix + ' ' + word + ' ' + suffix, '  ', ' '))) AS s
FROM (
    SELECT CAST(N'' AS nvarchar(100)) AS word
    UNION ALL SELECT NCHAR(1488) + NCHAR(1460) + NCHAR(1501)
    UNION ALL SELECT NCHAR(1513) + NCHAR(1464) + NCHAR(1473) + NCHAR(1488)
      + NCHAR(1463) + NCHAR(1500)
    UNION ALL SELECT NCHAR(1512) + NCHAR(1460) + NCHAR(1488) + NCHAR(1513)
      + NCHAR(1473) + NCHAR(1493) + NCHAR(1465) + NCHAR(1503)
    UNION ALL SELECT NCHAR(1489) + NCHAR(1461) + NCHAR(1468) + NCHAR(1503)
    UNION ALL SELECT NCHAR(1496) + NCHAR(1493) + NCHAR(1465) + NCHAR(1489)
    UNION ALL SELECT NCHAR(1490) + NCHAR(1463) + NCHAR(1468) + NCHAR(1490)
  ) AS W
CROSS JOIN (
    SELECT CAST(N'' AS nvarchar(100)) AS prefix
    UNION ALL SELECT N'11119'
    UNION ALL SELECT N'2229'
    UNION ALL SELECT N'339'
    UNION ALL SELECT N'49'
  ) AS P
CROSS JOIN (
    SELECT CAST(N'' AS nvarchar(100)) AS suffix
    UNION ALL SELECT N'55559'
    UNION ALL SELECT N'6669'
    UNION ALL SELECT N'779'
    UNION ALL SELECT N'89'
  ) AS S
ORDER BY s

ferchen
Starting Member

5 Posts

Posted - 2009-04-19 : 03:51:16

Hi all,
thanks for your help, but I see I missexplained the problem; The problem is a bad order between the rows.
The problem is shown within Arnolds message.
Thanks in advance.
Dani.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-04-19 : 08:48:25

quote:
Originally posted by ferchen

The problem is shown within Arnolds message.

SQL Team forums tends to mangle non-Latin text, so I'll extend my query to generate the a second string, but with the Hebrew word translated into English (well, hopefully: I'm relying on the examples in http://en.wikipedia.org/wiki/Hebrew_alphabet to be correct).

I've made a few less interesting changes so the data types of the output columns aren't too long.


SELECT s_english
FROM (
SELECT LTRIM(RTRIM(LTRIM(RTRIM(prefix + ' ' + word)) + ' ' + suffix)) AS s,
  LTRIM(RTRIM(LTRIM(RTRIM(prefix + ' ' + english_word)) + ' ' + suffix)) AS s_english,
  LTRIM(RTRIM(LTRIM(RTRIM(prefix + ' ' + CASE WHEN english_word = N'' THEN N'' ELSE
    NCHAR(0x202E) + english_word + NCHAR(0x202C) END)) + ' ' + suffix)) AS s_english_rtl
FROM (
    SELECT CAST(N'' AS nvarchar(8)) AS word, CAST(N'' AS nvarchar(8)) AS english_word
    UNION ALL SELECT NCHAR(1488) + NCHAR(1460) + NCHAR(1501), 'if'
    UNION ALL SELECT NCHAR(1513) + NCHAR(1464) + NCHAR(1473) + NCHAR(1488)
      + NCHAR(1463) + NCHAR(1500), 'asked'
    UNION ALL SELECT NCHAR(1512) + NCHAR(1460) + NCHAR(1488) + NCHAR(1513)
      + NCHAR(1473) + NCHAR(1493) + NCHAR(1465) + NCHAR(1503), 'first'
    UNION ALL SELECT NCHAR(1489) + NCHAR(1461) + NCHAR(1468) + NCHAR(1503), 'son'
    UNION ALL SELECT NCHAR(1496) + NCHAR(1493) + NCHAR(1465) + NCHAR(1489), 'good'
    UNION ALL SELECT NCHAR(1490) + NCHAR(1463) + NCHAR(1468) + NCHAR(1490), 'roof'
  ) AS W
CROSS JOIN (
    SELECT CAST(N'' AS nvarchar(5)) AS prefix
    UNION ALL SELECT N'11119'
    UNION ALL SELECT N'2229'
    UNION ALL SELECT N'339'
    UNION ALL SELECT N'49'
  ) AS P
CROSS JOIN (
    SELECT CAST(N'' AS nvarchar(5)) AS suffix
    UNION ALL SELECT N'55559'
    UNION ALL SELECT N'6669'
    UNION ALL SELECT N'779'
    UNION ALL SELECT N'89'
  ) AS S
) AS A
ORDER BY s

The result I get has s_english column in this order, which looks ok to me (bearing in mind that the rows are in the order of the string containing the Hebrew words).

quote:

s_english
--------------------

11119
11119 55559
11119 6669
11119 779
11119 89
11119 if
11119 if 55559
11119 if 6669
11119 if 779
11119 if 89
11119 son
11119 son 55559
11119 son 6669
11119 son 779
11119 son 89
11119 roof
11119 roof 55559
11119 roof 6669
11119 roof 779
11119 roof 89
11119 good
11119 good 55559
11119 good 6669
11119 good 779
11119 good 89
11119 first
11119 first 55559
11119 first 6669
11119 first 779
11119 first 89
11119 asked
11119 asked 55559
11119 asked 6669
11119 asked 779
11119 asked 89
2229
2229 55559
2229 6669
2229 779
2229 89
2229 if
2229 if 55559
2229 if 6669
2229 if 779
2229 if 89
2229 son
2229 son 55559
2229 son 6669
2229 son 779
2229 son 89
2229 roof
2229 roof 55559
2229 roof 6669
2229 roof 779
2229 roof 89
2229 good
2229 good 55559
2229 good 6669
2229 good 779
2229 good 89
2229 first
2229 first 55559
2229 first 6669
2229 first 779
2229 first 89
2229 asked
2229 asked 55559
2229 asked 6669
2229 asked 779
2229 asked 89
339
339 55559
339 6669
339 779
339 89
339 if
339 if 55559
339 if 6669
339 if 779
339 if 89
339 son
339 son 55559
339 son 6669
339 son 779
339 son 89
339 roof
339 roof 55559
339 roof 6669
339 roof 779
339 roof 89
339 good
339 good 55559
339 good 6669
339 good 779
339 good 89
339 first
339 first 55559
339 first 6669
339 first 779
339 first 89
339 asked
339 asked 55559
339 asked 6669
339 asked 779
339 asked 89
49
49 55559
49 6669
49 779
49 89
49 if
49 if 55559
49 if 6669
49 if 779
49 if 89
49 son
49 son 55559
49 son 6669
49 son 779
49 son 89
49 roof
49 roof 55559
49 roof 6669
49 roof 779
49 roof 89
49 good
49 good 55559
49 good 6669
49 good 779
49 good 89
49 first
49 first 55559
49 first 6669
49 first 779
49 first 89
49 asked
49 asked 55559
49 asked 6669
49 asked 779
49 asked 89
55559
6669
779
89
if
if 55559
if 6669
if 779
if 89
son
son 55559
son 6669
son 779
son 89
roof
roof 55559
roof 6669
roof 779
roof 89
good
good 55559
good 6669
good 779
good 89
first
first 55559
first 6669
first 779
first 89
asked
asked 55559
asked 6669
asked 779
asked 89

(175 row(s) affected)

So in what way does s look like it's in the wrong order?

If you're trying to assess the order by looking at the strings in column s displayed through a sophisticated bidirectional text rendering algorithm, then it may look confusing. In the above query, try selecting s instead of s_english at the outermost level and comparing the result you get in SSMS in text mode and grid mode.

In SSMS 2005 the grid result view displays a glyph for each code point in the string in strict left-to-right order, whereas the text result view uses a bidirectional renderer. With the text result try moving the cursor through the lines of s, particularly a line that contains both a prefix numeric, a word and a suffix numeric. You should find (if the text view is defaulting to left-to-right) that the line containing, "339 WORD 55559" is rendered like "339 55559 DROW", but that the cursor follows the string order rather than the visual order.

So why is the suffix number appearing to the left of the Hebrew word? In short it's the interaction between strongly right-to-left text (the Hebrew) being followed by weakly left-to-right text (the number).

You can get almost the same effect with the output for english words by inserting a Unicode RLO before the word and a PDF (pop directional formatting) after: that's what's in the s_english_rtl column.

ferchen
Starting Member

5 Posts

Posted - 2009-04-20 : 10:02:17

Hi Arnold,
thanks for your reply, I'm still trying to understand it -:)
Dani.

p_shripal
Starting Member

2 Posts

Posted - 2009-09-09 : 04:18:59

quote:

Originally posted by madhivanan

Numbers last


select data from
(
	select 'test' as data union all
	select '345' union all
	select 'as345' union all
	select 'b5' union all
	select 'f5' union all
	select 'd445' union all
	select '65' 
) as t
order by case when data not like '%[^0-9]%' then data*1 else 1 end, data

Madhivanan

Failing to plan is Planning to fail

Shripal

p_shripal
Starting Member

2 Posts

Posted - 2009-09-09 : 04:20:35

Could you let me know query for numbers first for your below post.

quote:

Originally posted by madhivanan

Numbers last


select data from
(
	select 'test' as data union all
	select '345' union all
	select 'as345' union all
	select 'b5' union all
	select 'f5' union all
	select 'd445' union all
	select '65' 
) as t
order by case when data not like '%[^0-9]%' then data*1 else 1 end, data

Madhivanan

Failing to plan is Planning to fail

Shripal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 05:33:59

quote:
Originally posted by p_shripal

Could you let me know query for numbers first for your below post.

quote:
Originally posted by madhivanan

Numbers last

select data from
(
select 'test' as data union all
select '345' union all
select 'as345' union all
select 'b5' union all
select 'f5' union all
select 'd445' union all
select '65'
) as t
order by case when data not like '%[^0-9]%' then data*1 else 1 end, data

Madhivanan

Failing to plan is Planning to fail

Shripal

Not sure if this works for all set of data

select data from
(
	select 'test' as data union all
	select '345' union all
	select 'as345' union all
	select 'b5' union all
	select 'f5' union all
	select 'd445' union all
	select '65' 
) as t
order by case when data like '%[^0-9]%' then data else '1' end, len(data)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources