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>

7 comments:

Delfino N. 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.

bike arenal said...

Costa rica tours, shopping online, costa rica fishing, jaco fishing charters, costa rica whale watching, costa rica dolphin watching, costa rica party boat, panama fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing, costa rica los suenos fishing, fishing costa rica los suenos, los suenos fishing charters, costa rica fishing los suenos, los suenos costa rica fishing charters, costa rica los suenos fishing, Los suenos fishing, Fishing costa rica los suenos, Costa Rica Rafting,
http://www.kingtours.com/.

Ephesus said...

I loved every moment of our tour. Istanbul, cappadocia and ephesus are fantastic city. We saw so many historical places and guide was very informative. We definitely recommend it. http://www.packagetoursturkey.com - http://www.touroperatoristanbul.com We visited Istanbul for the first time on your tour turkey tours we are from Cappadocia tours Greece and we just loved it! istanbul tours We finally were able to see sights that we had only heard from istanbul tourism friends who visited I have to say that this trip was fantastic. I had a great time in Turkey. Everything went on very well actually much better than we expected istanbul travel Our guide was very friendly and ephesus tours he knew all details of history. Thanks for erything.

istanbul said...

TTG Travel Agency is a leading travel agency and tour operator in istanbul Turkey with more than seven years of professional travel service. Because of our dedication and commitment to your needs istanbul travel we are able to offer you better quality of service at lower prices than other operators. http://www.istanbulhotels.com We provide hundreds of elaborately pre-designed Turkey tour packages, Turkey tours discounted, Guided turkey hotels and Turkey Tours, and we also provide customized travel service that is organized according to your specific needs and requirements http://www.istanbulhotels.com Our tours are professionally guided, intimate, and safe.

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

Hacker said...

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