pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Emi Lu <emilu@encs.concordia.ca>
To: Ozer, Pam <pozer@automotive.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sorting Issue
Date: Mon, 09 May 2011 15:52:12 -0400
Message-ID: <4DC845EC.1020806@encs.concordia.ca> (raw)
In-Reply-To: <216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F7@mail-001.corp.automotive.com>
References: <216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F1@mail-001.corp.automotive.com>
	<4DC84284.4000903@encs.concordia.ca>
	<216FFB77CBFAEE4B8EE4DF0A939FF1D14F85F7@mail-001.corp.automotive.com>


> That works. Why?

http://www.postgresql.org/docs/current/static/functions-string.html


split_part(string text, delimiter text, field int)	text	Split string on 
delimiter and return the given field (counting from one) 
split_part('abc~@~def~@~ghi', '~@~', 2)	def

Emi


> -----Original Message-----
> From: Emi Lu [mailto:emilu@encs.concordia.ca]
> Sent: Monday, May 09, 2011 12:38 PM
> To: Ozer, Pam
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Sorting Issue
>
>> I have the following query
>>
>> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
>>
>> from VehicleTrimAbbreviated
>>
>> Where vehicleTrimAbbreviated like 'CX%'
>>
>> order by VehicleTrimAbbreviated asc
>>
>> Results:
>>
>> 532;"CX Hatchback"
>>
>> 536;"CXL Minivan"
>>
>> 3255;"CXL Premium Sedan"
>>
>> 537;"CXL Sedan"
>>
>> 538;"CXL Sport Utility"
>>
>> 3319;"CXL Turbo Sedan"
>>
>> 533;"CX Minivan"
>>
>> 1959;"CX Plus Minivan"
>>
>> 534;"CX Sedan"
>>
>> 535;"CX Sport Utility"
>>
>> 539;"CXS Sedan"
>>
>> Why would this not sort correctly? All the CX should be first, then
> CXL,
>> Then CXS
>
> Would you mind try:
>
> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
> from VehicleTrimAbbreviated
> Where vehicleTrimAbbreviated like 'CX%'
> order by
>
> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
> split_part(VehicleTrimAbbreviated, ' ', 2) asc;
>
>



view thread (17+ messages)  latest in thread

Message-ID: <4DC845EC.1020806@encs.concordia.ca>
Permalink:  ../4DC845EC.1020806@encs.concordia.ca/
Also on:    postgresql.org/message-id/4DC845EC.1020806@encs.concordia.ca

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: emilu@encs.concordia.ca, pozer@automotive.com
  Subject: Re: Sorting Issue
  In-Reply-To: <4DC845EC.1020806@encs.concordia.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox