Thursday, November 1

Oracle SQL to return a alphabetical subset

Yes, I know, I've been waaay bad at blogging lately. Been busy working on new projects at work, upgrading applications to Java1.5 containers (Websphere 6.1, Weblogic 9.2 etc). On the fun side we've got an Azul Java-acceleration box, that really needs a couple of blog entries!

Anyway, got asked if there was a way to return a resultset of data based on the leading character, the use case was to ignore all strings starting with a,b, or d and return e to z.

Fairly straight forward but a good SQL to have.
I simply grab the first character in the varchar and compare it's ASCII value to the ASCII value D.
SQL> select * from t;

A
----------
Atest
Btest
Etest
Htest
Wtest
Dtest
dtest
SQL> with data as (
2 select ascii(upper(substr(a,1,1))) a_val,a from t
3 )
4 select * from data where a_val not between ascii('A') and ascii('D') order by a
5 /

A_VAL A
---------- ----------
69 Etest
72 Htest
87 Wtest
SQL>

4 comments:

Anonymous said...

I think your result is not correct, I got a different result:


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> with t as (
2 select 'Atest' a from dual union all
3 select 'Btest' from dual union all
4 select 'Etest' from dual union all
5 select 'Htest' from dual union all
6 select 'Wtest' from dual union all
7 select 'Dtest' from dual union all
8 select 'dtest' from dual),
9 data as (
10 select ascii (upper (substr (a, 1, 1) ) ) a_val, a
11 from t)
12 select ascii ('A'), ascii ('D'), data.*
13 from data
14 where a_val not between ascii ('A') and ascii ('D')
15 order by a;

ASCII('A') ASCII('D') A_VAL A
---------- ---------- ---------- -----
65 68 69 Etest
65 68 72 Htest
65 68 87 Wtest


I think the values of Dtest and dtest should not be displayed because their ascii value (68) is between 65 and 68.

pedro velasquez said...

I've been testing out the Ubuntu network install environment today. All you need to use the PXE installer is to configure a DHCP server and TFTP server on your network,dominical costa rica tours if you want a more hands off install you can use a "preseed" file. Quite easy to use and it is much more flexible then the ananconda kickstart files.
Ubuntu actually support the kickstart file format as well, so if you don't want to learn the new preconfigure "preseed" format you can just write a normal kickstart syntax file and netboot Ubuntu exactly the same way you would a Redhat/Centos/Fedora box.
http://www.dominicalcostaricatours.com

Mrk said...

ooo dostum güzelbir paylaşımda bulunmuştun konuharika istanbulda ismek kursları elsanatı bilgisayar yemek spor güzellik ibb
Kurdele Nakısı kurdele işi nakıs kursu ücretsiz eğitim

Unknown said...

this is want comenet helo thank blogs admins post come turkey antalya
Marmaris Hotel turkey Holidayscomen travelmarmaris toursphotos fine city see pool goo