SQL - sorting alphanumeric values which are in English and Japanese

Question

I'm not sure if its possible but i have column which contains some English and Japanese values like this

**section**
AHU-1-1
AHU-1-10
AHU-1-1-1
AHU-1-1-2
AHU-1-2
AHU-1-3
AHU-1-8
AHU 1
AHU 11
AHU 2
賃貸人側 1連絡先
更新連絡先

Now i want an output with sorted values as -

**Section**
AHU 1
AHU 2
AHU 11
AHU-1-1
AHU-1-2
AHU-1-3
AHU-1-8
AHU-1-10
AHU-1-1-1
AHU-1-1-2
賃貸人側 1連絡先
更新連絡先

i tried following query but it is only working for values which are in English language. Not for Japanese language values.

SELECT Section
FROM dbo.Section
ORDER BY LEFT(replace(Section,'-','0'),PATINDEX('%[0-9]%',replace(Section,'-    ','0'))-1), -- alphabetical sort
     CONVERT(INT,SUBSTRING(replace(Section,'-','0'),PATINDEX('%[0-9]%',replace(Section,'-','0')),LEN(replace(Section,'-','0')))) -- numerical sort

Is it possible to sort in both the languages?

Just to get some things clear: Your table `dbo.Section` contains (in a `NVARCHAR`-column?) mixed values. What is the sort order if you just use `ORDER BY Section`? Are there *mixed values* too? Are all the English entries starting with "AHU" and are followed by numbers? Is there any indication which is Japanese and which is English, or do you have to guess after occurence of characters? Ho do you need the sorting of "AHU-1-2", "AHU-1-5" and "AHU-10-1"? — Shnugo, Jun 24 '16 at 09:33
@Shnugo Yes it contains NVARCHAR column mixed values. English values can start with other than "AHU". But format will be similar. As it will always start with alphabet and then it can contain number, or special character as ("-") or space. No there is no indication as which is Japanese or which is english. — omkar patade, Jun 24 '16 at 10:13

gofr1 · Accepted Answer · 2016-06-24T10:33:10.790

This will work for SQL Server 2012.

A little bit weird but it works for given sample:

DECLARE @xml xml
;WITH cte AS (
SELECT *
FROM (VALUES
(N'AHU-1-1'),(N'AHU-1-10'),(N'AHU-1-1-1'),(N'AHU-1-1-2'),(N'AHU-1-2'),
(N'AHU-1-3'),(N'AHU-1-8'),(N'AHU 1'),(N'AHU 11'),(N'AHU 2'),
(N'賃貸人側 1連絡先'),(N'更新連絡先')
) as t(section)
)


SELECT @xml = (
    SELECT CAST('<i id="'+section +'">'+ REPLACE('<b>'+REPLACE(section,'-','</b><b>')+'</b>',' ','</b><b>') +'</i>' as xml)
    FROM cte
    FOR XML PATH('')
)

SELECT section
FROM (
    SELECT  i.b.value('@id','nvarchar(20)') as section,
            i.b.value('b[1]','nvarchar(20)') as a,
            TRY_CAST(i.b.value('b[2]','nvarchar(20)') as int) as b,
            TRY_CAST(i.b.value('b[3]','nvarchar(20)') as int) as c,
            TRY_CAST(i.b.value('b[4]','nvarchar(20)') as int) as d
    FROM @xml.nodes('/i') as i(b)
    ) as p
ORDER BY (CASE WHEN b IS NULL and c IS NULL and d is NULL THEN 1 ELSE 0 END), d, c, b

Output:

section
AHU 1
AHU 2
AHU 11
AHU-1-1
AHU-1-2
AHU-1-3
AHU-1-8
AHU-1-10
AHU-1-1-1
AHU-1-1-2
賃貸人側 1連絡先
更新連絡先

For SQL Server 2008 and up use:

SELECT section
FROM (
    SELECT  i.b.value('@id','nvarchar(20)') as section,
            i.b.value('b[1]','nvarchar(20)') as a,
            i.b.value('b[2] cast as xs:int ?','int') as b,
            i.b.value('b[3] cast as xs:int ?','int') as c,
            i.b.value('b[4] cast as xs:int ?','int') as d
    FROM @xml.nodes('/i') as i(b)
    ) as p
ORDER BY (CASE WHEN b IS NULL and c IS NULL and d is NULL THEN 1 ELSE 0 END), d, c, b

Not sure.. But its giving error on my side at the sub query @xml.nodes — omkar patade, Jun 24 '16 at 10:17
Greate, +1 from my side... Did not know the `cast as xs:int ?` Do you have a link to some documentation? — Shnugo, Jun 24 '16 at 12:58
Thanks! At first I encountered with `xs` (`xs:base64Binary`) [here](https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql) on SO. Than I found article on [MSDN](https://msdn.microsoft.com/en-us/library/ms191231.aspx). Unfortunately I use this casting hardly ever :( and it was nice to remember about it, during finding the answer to this question! — gofr1, Jun 24 '16 at 13:09

score 0 · Answer 2 · answered Jun 24 '16 at 09:23

You can add a collate WHATEVER_COLLATION to the end of your order by clause...

I am not sure of the specific collation you will want to use, not knowing your data, however you should find a reference on the differences here: https://msdn.microsoft.com/en-us/library/ff848763.aspx

SQL - sorting alphanumeric values which are in English and Japanese

2 Answers2