Help needed on select issue from Chinese_RPC_Stroke_90 table

Question

Help needed on select issue from Chinese_RPC_Stroke_90 table

AhTu_SQL2k+

Ten Centuries

Points: 1012
More actions
October 7, 2013 at 12:40 am

#283990

Hi there,
I have a table code page in Chinese_PRC_Stroke_90_CI_AS and I am facing a 'select' problem now when data has special character in the field.
Table data stores the following,
RYP1836-K
RYP1836-K1
RYP1836A-K
RYP1836A-K1
RYP1836B-K
RYP1836B-K1
RYP1836C-K
RYP1836C-K1
RYP1836D-K
RYP1836D-K1
Scenarios,
1) it returns 0 record when I issue statement like this
select part_code from s_stkmst
where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1'
order by part_code
2) it returns only 6 records when I applies collation order (miss out first 2 records)
select REPLACE(part_code,'-','#'),part_code, source from s_stkmst
where part_code>= REPLACE('RYP1836-K','-','#') AND part_code<=REPLACE('RYP1836D-K1','-','#')
order by part_code collate SQL_Latin1_General_Cp437_BIN
RYP1836A#KRYP1836A-K
RYP1836A#K1RYP1836A-K1
RYP1836B#KRYP1836B-K
RYP1836B#K1RYP1836B-K1
RYP1836C#KRYP1836C-K
RYP1836C#K1RYP1836C-K1
Any idea to overcome this?
Thanks in advance
Wilson
Together, we can make wonders!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sqlblues Grasshopper Points: 17 More actions · Answer 1

This has go nothing to do with the collation it is just that when you order the parts list using standard alphanumeric ordering you get a different order to the one you expect.

Order returned by SQL even with a default Latin collation:

RYP1836A-K

RYP1836A-K1

RYP1836B-K

RYP1836B-K1

RYP1836C-K

RYP1836C-K1

RYP1836D-K

RYP1836D-K1

RYP1836-K

RYP1836-K1

As you can see there is nothing between RYP1836D-K1 and RYP1836-K and in fact RYP1836-K is listed after RYP1836D-K1

sqlblues Grasshopper Points: 17 More actions · Answer 2

The only solution is to amend you part numbering to something that will sort the way you expect

drop table #s_stkmst

create table #s_stkmst(part_code nvarchar(50) , part_code_Latin nvarchar(50))

insert #s_stkmst

values ('RYP1836-K','RYP1836 -K0')

, ('RYP1836-K1','RYP1836 -K1')

, ('RYP1836A-K','RYP1836A-K0')

, ('RYP1836A-K1','RYP1836A-K1')

, ('RYP1836B-K','RYP1836B-K0')

, ('RYP1836B-K1','RYP1836B-K1')

, ('RYP1836C-K','RYP1836C-K0')

, ('RYP1836C-K1','RYP1836C-K1')

, ('RYP1836D-K','RYP1836D-K0')

, ('RYP1836D-K1','RYP1836D-K1')

select part_code from #s_stkmst

--where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1'

order by part_code

select part_code, part_code_Latin from #s_stkmst

--where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1'

order by part_code_Latin

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 3

sqlblues (10/7/2013)
The only solution is to amend you part numbering to something that will sort the way you expect
drop table #s_stkmst
create table #s_stkmst(part_code nvarchar(50) , part_code_Latin nvarchar(50))
insert #s_stkmst
values ('RYP1836-K','RYP1836 -K0')
, ('RYP1836-K1','RYP1836 -K1')
, ('RYP1836A-K','RYP1836A-K0')
, ('RYP1836A-K1','RYP1836A-K1')
, ('RYP1836B-K','RYP1836B-K0')
, ('RYP1836B-K1','RYP1836B-K1')
, ('RYP1836C-K','RYP1836C-K0')
, ('RYP1836C-K1','RYP1836C-K1')
, ('RYP1836D-K','RYP1836D-K0')
, ('RYP1836D-K1','RYP1836D-K1')
select part_code from #s_stkmst
--where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1'
order by part_code
select part_code, part_code_Latin from #s_stkmst
--where part_code>= 'RYP1836-K' AND part_code <= 'RYP1836D-K1'
order by part_code_Latin

Thanks for your advice.

I realized this and I had done that in my PC and it works as you said. However, I cannot alter the code page at the production server because there are many tables having the same field. I truly worry other queries in my system will produce 'mismatch of collation' error.

Together, we can make wonders!

sqlblues Grasshopper Points: 17 More actions · Answer 4

As before this problem in not to do with collation as Latin characters sort the same whether the collation is Latin or Chinese. The problem is your part numbers where RYP1836- will sort after RYP1836A. here is another example (addendum to the code in my previous post)

select REPLACE(part_code,'-','#'),part_code, part_code_Latin from #s_stkmst

where part_code_Latin>= REPLACE('RYP1836 -K0','-','#') AND part_code_Latin<=REPLACE('RYP1836D-K1','-','#')

order by part_code collate SQL_Latin1_General_Cp437_BIN

AhTu_SQL2k+ Ten Centuries Points: 1012 More actions · Answer 5

Thanks sqlblues.

I had figured out, if I change the query as below, the list has become complete.

select REPLACE(part_code,'-','#'),part_code from s_stkmst

where REPLACE(part_code,'-','#')>= REPLACE('RYP1836-K','-','#') AND REPLACE(part_code,'-','#')<=REPLACE('RYP1836D-K1','-','#')

order by part_code

Together, we can make wonders!