Help needed on select issue from Chinese_RPC_Stroke_90 table

Question

Post reply

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#K RYP1836A-K

RYP1836A#K1 RYP1836A-K1

RYP1836B#K RYP1836B-K

RYP1836B#K1 RYP1836B-K1

RYP1836C#K RYP1836C-K

RYP1836C#K1 RYP1836C-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!