Thread: How to sort strings containing a dot?

How to sort strings containing a dot?

From

Pierre LEBRECH

Date:

25 March 2008, 15:29:13

Hello,

I want to sort strings containing a dot but by taking care of this dot
like any other character.

example :

Currently, I get this after the sort :

co.aaa
co.abb
co.cab
com
co.ment
com.enta

But I would like to get this :

co.aaa
co.abb
co.cab
co.ment
com
com.enta

How I can do this?
Thank you

Re: How to sort strings containing a dot?

From

"Roberts, Jon"

Date:

25 March 2008, 15:38:06

create table t (name varchar);

insert into t values ('co.aaa');
insert into t values ('co.abb');
insert into t values ('co.cab');
insert into t values ('com');
insert into t values ('co.ment');
insert into t values ('com.enta');

select name from t order by replace(name, '.', '');


Jon

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Pierre LEBRECH
> Sent: Tuesday, March 25, 2008 9:56 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] How to sort strings containing a dot?
>
> Hello,
>
> I want to sort strings containing a dot but by taking care of this dot
> like any other character.
>
> example :
>
> Currently, I get this after the sort :
>
> co.aaa
> co.abb
> co.cab
> com
> co.ment
> com.enta
>
> But I would like to get this :
>
> co.aaa
> co.abb
> co.cab
> co.ment
> com
> com.enta
>
> How I can do this?
> Thank you
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: How to sort strings containing a dot?

From

Tom Lane

Date:

25 March 2008, 15:58:58

Pierre LEBRECH <pierre.lebrech@laposte.net> writes:
> I want to sort strings containing a dot but by taking care of this dot
> like any other character.

I suspect what you really want is C locale, and what you've got is
some other locale that uses "dictionary" sort rules.  Try "show lc_collate".

            regards, tom lane

Re: How to sort strings containing a dot?

From

Pierre LEBRECH

Date:

25 March 2008, 16:05:40

Thanks Jon, but it does not give me what I want.

But, this gave me an idea : I replaced the select statement by this one :

select name from t order by replace(name, '.', 'z');

And this time it works. With 'z', I get 'co' before 'com'. If I set an 'a', then I get the 'com' before 'co'. Cool!

examples :

dns=> select name from t order by replace(name, '.', 'z');
   name
----------
 com
 com.enta
 co.aaa
 co.abb
 co.cab
 co.ment
(6 lines)

dns=> select name from t order by replace(name, '.', 'a');
   name
----------
 co.aaa
 co.abb
 co.cab
 co.ment
 com
 com.enta
(6 lines)


Thank you.

Roberts, Jon wrote :
> create table t (name varchar);
>
> insert into t values ('co.aaa');
> insert into t values ('co.abb');
> insert into t values ('co.cab');
> insert into t values ('com');
> insert into t values ('co.ment');
> insert into t values ('com.enta');
>
> select name from t order by replace(name, '.', '');
>
>
> Jon
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Pierre LEBRECH
>> Sent: Tuesday, March 25, 2008 9:56 AM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] How to sort strings containing a dot?
>>
>> Hello,
>>
>> I want to sort strings containing a dot but by taking care of this dot
>> like any other character.
>>
>> example :
>>
>> Currently, I get this after the sort :
>>
>> co.aaa
>> co.abb
>> co.cab
>> com
>> co.ment
>> com.enta
>>
>> But I would like to get this :
>>
>> co.aaa
>> co.abb
>> co.cab
>> co.ment
>> com
>> com.enta
>>
>> How I can do this?
>> Thank you
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>

Re: How to sort strings containing a dot?

From

Pierre LEBRECH

Date:

25 March 2008, 16:42:14

By the way, I have just inserted a duplicate. Then I have run the select statement with distinct and I got an error.


select distinct name from t order by replace(name, '.', 'a');
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

How one can solve this?


Note : If I remove the distinct word, It works and I get this :

select name from t order by replace(name, '.', 'a');
   name
----------
 co.aaa
 co.abb
 co.cab
 co.ment
 co.ment
 com
 com.enta
(7 lines)


Roberts, Jon wrote :
> create table t (name varchar);
>
> insert into t values ('co.aaa');
> insert into t values ('co.abb');
> insert into t values ('co.cab');
> insert into t values ('com');
> insert into t values ('co.ment');
> insert into t values ('com.enta');
>
> select name from t order by replace(name, '.', '');
>
>
> Jon
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Pierre LEBRECH
>> Sent: Tuesday, March 25, 2008 9:56 AM
>> To: pgsql-general@postgresql.org
>> Subject: [GENERAL] How to sort strings containing a dot?
>>
>> Hello,
>>
>> I want to sort strings containing a dot but by taking care of this dot
>> like any other character.
>>
>> example :
>>
>> Currently, I get this after the sort :
>>
>> co.aaa
>> co.abb
>> co.cab
>> com
>> co.ment
>> com.enta
>>
>> But I would like to get this :
>>
>> co.aaa
>> co.abb
>> co.cab
>> co.ment
>> com
>> com.enta
>>
>> How I can do this?
>> Thank you
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>

Re: How to sort strings containing a dot?

From

"Adam Rich"

Date:

25 March 2008, 16:47:00

> By the way, I have just inserted a duplicate. Then I have run the
> select statement with distinct and I got an error.
>
> How one can solve this?
>

Does this work?

select distinct name
from (  select name from t order by replace(name, '.', 'a')) as t2

Re: How to sort strings containing a dot?

From

Pierre LEBRECH

Date:

25 March 2008, 16:58:42

Thanks Adam. No, It doesn't.

But I have found a solution which works :

select name from (select distinct name from t) as name order by replace(name, '.', 'a');
   name
----------
 co.aaa
 co.abb
 co.cab
 co.ment
 com
 com.enta
(6 lines)


Thanks to all of you.


Adam Rich wrote :
>> By the way, I have just inserted a duplicate. Then I have run the
>> select statement with distinct and I got an error.
>>
>> How one can solve this?
>>
>
> Does this work?
>
> select distinct name
> from (  select name from t order by replace(name, '.', 'a')) as t2
>
>
>

Re: How to sort strings containing a dot?

From

"Roberts, Jon"

Date:

25 March 2008, 17:08:24

select name from t group by name order by replace(name, '.', 'a');

I personally don't use distinct very often as group by is usually more
flexible and quicker to add aggregates to the query.


Jon
> -----Original Message-----
> From: Pierre LEBRECH [mailto:pierre.lebrech@laposte.net]
> Sent: Tuesday, March 25, 2008 11:42 AM
> To: Roberts, Jon
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] How to sort strings containing a dot?
>
> By the way, I have just inserted a duplicate. Then I have run the
select
> statement with distinct and I got an error.
>
>
> select distinct name from t order by replace(name, '.', 'a');
> ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in
select
> list
>
> How one can solve this?
>
>
> Note : If I remove the distinct word, It works and I get this :
>
> select name from t order by replace(name, '.', 'a');
>    name
> ----------
>  co.aaa
>  co.abb
>  co.cab
>  co.ment
>  co.ment
>  com
>  com.enta
> (7 lines)
>
>
> Roberts, Jon wrote :
> > create table t (name varchar);
> >
> > insert into t values ('co.aaa');
> > insert into t values ('co.abb');
> > insert into t values ('co.cab');
> > insert into t values ('com');
> > insert into t values ('co.ment');
> > insert into t values ('com.enta');
> >
> > select name from t order by replace(name, '.', '');
> >
> >
> > Jon
> >
> >> -----Original Message-----
> >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> >> owner@postgresql.org] On Behalf Of Pierre LEBRECH
> >> Sent: Tuesday, March 25, 2008 9:56 AM
> >> To: pgsql-general@postgresql.org
> >> Subject: [GENERAL] How to sort strings containing a dot?
> >>
> >> Hello,
> >>
> >> I want to sort strings containing a dot but by taking care of this
dot
> >> like any other character.
> >>
> >> example :
> >>
> >> Currently, I get this after the sort :
> >>
> >> co.aaa
> >> co.abb
> >> co.cab
> >> com
> >> co.ment
> >> com.enta
> >>
> >> But I would like to get this :
> >>
> >> co.aaa
> >> co.abb
> >> co.cab
> >> co.ment
> >> com
> >> com.enta
> >>
> >> How I can do this?
> >> Thank you
> >>
> >>
> >> --
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >

Re: How to sort strings containing a dot?

From

Steve Wampler

Date:

25 March 2008, 18:09:24

Pierre LEBRECH wrote:
> But I have found a solution which works :
>
> select name from (select distinct name from t) as name order by replace(name, '.', 'a');

Shouldn't you replace '.' with a character whose collating sequence is strictly less than
'a'?  This solution looks to me as though it might not get ,e.g., 'coast' and 'co.st' in
the desired order except by chance.

--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.