Help on string sorting

Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra MudugalPlease post a repro script.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
Hello
I have a customer table created in the mssql server 2000, in this table i am
using
customerid as "CUST_0001" and so on..
When i say order by on customerid column, the IDs are not sorted in a proper
way
Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
order by customerid puts "CUST_0010" in the second row. but it supposed to
in the 10thr row.
Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting on
this
column. but nothing helped.
Please advise me on this and what way it can be achived. I am not going to
change the format of the ID which i am storing. that confirmed. but i need
that to be sorted
in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are changed.
http://support.microsoft.com/defaul...kb;en-us;322112
the above link where i looked to setting up the COLLATION.
Hope to receive your advice as soon. Its urgent.
--
With Regards,
Raghavendra Mudugal|||Hi
This does sounds like you have a binary collation and mixed case data. You
may want to change the collation to something more intuitive or use
something like:
SELECT customerid
FROM MyTable
ORDER BY customerid COLLATE Latin1_General_CI_AS
See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
sample data.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
> Hello
> I have a customer table created in the mssql server 2000, in this table i
> am
> using
> customerid as "CUST_0001" and so on..
> When i say order by on customerid column, the IDs are not sorted in a
> proper
> way
> Say for e.g if i have 10 customers list as "CUST_0001" to "CUST_0010" then
> order by customerid puts "CUST_0010" in the second row. but it supposed to
> in the 10thr row.
> Even I tried setting the Collation to 30 and 50 BIN and Latin BIN sorting
> on
> this
> column. but nothing helped.
> Please advise me on this and what way it can be achived. I am not going to
> change the format of the ID which i am storing. that confirmed. but i need
> that to be sorted
> in proper way. But this was fine with SQL 6.5, in SQL 2000 lots are
> changed.
> http://support.microsoft.com/defaul...kb;en-us;322112
> the above link where i looked to setting up the COLLATION.
> Hope to receive your advice as soon. Its urgent.
> --
> With Regards,
> Raghavendra Mudugal|||Hello John Bell,
Thanks for your reply.
I tried your suggetion,, but its not helping me in any way.
I looked in the "SQL Collation Name" list in SQL books on-line and tried
all names giving with that "customerid" column, but all resulted the same,
no difference at all. and its still showing the "CUST_0010" customer id
in the 2nd place.
please help.
With Regards,
Raghavendra Mudugal
"John Bell" wrote:

> Hi
> This does sounds like you have a binary collation and mixed case data. You
> may want to change the collation to something more intuitive or use
> something like:
> SELECT customerid
> FROM MyTable
> ORDER BY customerid COLLATE Latin1_General_CI_AS
> See http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and
> sample data.
> John
> "Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
> in message news:EF13C28C-FE05-4F89-9181-EE3A649C9627@.microsoft.com...
>
>|||If it always starts with CUST_ and it always ends with digits you could
do
order by convert(int,replace(CustomerID,'CUST_",''))
http://sqlservercode.blogspot.com/|||well an annoying cheat kinda way u could do it i suppose is in the slect
statement, sort out the integers with substring (CustomerID, 6,10)-(is that
right? i dont remember the format it was, anyway u get the concept, the
column name then the character it starts and character it ends and all shoul
d
be integers) and Cast() the whole thing as INT, and make is AS SortNumber or
something. THen order by SortNumber instead of costomerID
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Lol well SQL beat me to it but i recommend CAST function over CONVERT becaus
e
the books say that CONVERT was only used for backward compatibility and CAST
should perform faster and avoid getting obsolete in later SQL versions
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1|||Hello,
And thanks for your reply. I am ok with this Idea, but is this the only way
to order the customerid just by taking those four digits and just sorting th
em
as numerical sort and use it?
Because, presently I have 1.5 to 1.6 lac of customers listed in the database
.
And is that code works faster? I had no problems using the string sort in
SQL6.5, and i never used to convert the id to int . in 2000 lots of things a
re
changed.
Hope to receive your valuabel response as soon.
Thanks for your time.
--
With Regards,
Raghavendra Mudugal
"SQL" wrote:

> If it always starts with CUST_ and it always ends with digits you could
> do
> order by convert(int,replace(CustomerID,'CUST_",''))
> http://sqlservercode.blogspot.com/
>|||Hi
You may want to post DDL and sample that will re-create your problem.
John
"Raghavendra Mudugal" <RaghavendraMudugal@.discussions.microsoft.com> wrote
in message news:8ED10297-AFDE-4502-AC78-6C2729BDA1FB@.microsoft.com...[vbcol=seagreen]
> Hello,
> And thanks for your reply. I am ok with this Idea, but is this the only
> way
> to order the customerid just by taking those four digits and just sorting
> them
> as numerical sort and use it?
> Because, presently I have 1.5 to 1.6 lac of customers listed in the
> database.
> And is that code works faster? I had no problems using the string sort in
> SQL6.5, and i never used to convert the id to int . in 2000 lots of things
> are
> changed.
> Hope to receive your valuabel response as soon.
> Thanks for your time.
> --
> With Regards,
> Raghavendra Mudugal
>
> "SQL" wrote:
>sql