Thread: Re: [SQL] case-insensitive SORT BY?
Cheat. Get the name back, *AND* a second "field" with the name in upper case, and then sort by that upper-cased name. EG: "select name, upper(name) as key where name like '%clinton%' sort by key" At 3:53 PM 9/22/98, Patrick Giagnocavo wrote: >I am trying to ensure that when I do a SORT BY I get back the results in >normal sorted order instead of case-sensitive order. The WHERE clause >contains a LIKE '%..%' so I cannot use UPPER here in a way that does >what I want. > >e.g. , given > >A, B, b, a > >as data, the normal SORT BY behavior returns > >a >b >A >B > >How do I make it return > >a >A >b >B > >instead? -- -- -- "TANSTAAFL" Rich lynch@lscorp.com
Perhaps this should go in the FAQ? I asked it myself several months ago. Also note that you probably want to sort by key followed by name ( ie ... sort by key, name) so that you don't run the risk of getting something like: a A B b Chris -- I am at one with my duality. On Tue, 22 Sep 1998, Richard Lynch wrote: > Cheat. Get the name back, *AND* a second "field" with the name in upper > case, and then sort by that upper-cased name. EG: > > "select name, upper(name) as key where name like '%clinton%' sort by key" > > At 3:53 PM 9/22/98, Patrick Giagnocavo wrote: > >I am trying to ensure that when I do a SORT BY I get back the results in > >normal sorted order instead of case-sensitive order. The WHERE clause > >contains a LIKE '%..%' so I cannot use UPPER here in a way that does > >what I want. > > > >e.g. , given > > > >A, B, b, a > > > >as data, the normal SORT BY behavior returns > > > >a > >b > >A > >B > > > >How do I make it return > > > >a > >A > >b > >B > > > >instead? > > -- > -- > -- "TANSTAAFL" Rich lynch@lscorp.com > > >
Here is a 6.4 tidbit. select name where name like '%clinton%' order by upper(name), name; is allowed. (i.e. functions are allowed in the ORDER clause) Like you said, the the second attribute in the ORDER clause will be the tie breaker. Chris Johnson wrote: > Perhaps this should go in the FAQ? I asked it myself several months ago. > > Also note that you probably want to sort by key followed by name ( ie ... > sort by key, name) so that you don't run the risk of getting something > like: > > a > A > B > b > > Chris > -- > I am at one with my duality. > > On Tue, 22 Sep 1998, Richard Lynch wrote: > > > Cheat. Get the name back, *AND* a second "field" with the name in upper > > case, and then sort by that upper-cased name. EG: > > > > "select name, upper(name) as key where name like '%clinton%' sort by key" > > > > At 3:53 PM 9/22/98, Patrick Giagnocavo wrote: > > >I am trying to ensure that when I do a SORT BY I get back the results in > > >normal sorted order instead of case-sensitive order. The WHERE clause > > >contains a LIKE '%..%' so I cannot use UPPER here in a way that does > > >what I want. > > > > > >e.g. , given > > > > > >A, B, b, a > > > > > >as data, the normal SORT BY behavior returns > > > > > >a > > >b > > >A > > >B > > > > > >How do I make it return > > > > > >a > > >A > > >b > > >B > > > > > >instead? > > > > -- > > -- > > -- "TANSTAAFL" Rich lynch@lscorp.com > > > > > >