Prev: oracle and database ebooks
Next: Oracle 10g Lite & .NET
From: Charles Hooper on 30 Dec 2009 16:49
On Dec 30, 12:28 pm, joel garry <joel-ga...(a)home.com> wrote:
> On Dec 30, 5:31 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> LOL, you should write a book! "Bad SQL! Bad, bad!"
>
> > Something tells me you want to do it the easy way.  See if you can do
> > anything with these functions:
> > REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > *Always* post the DDL and DML to re-create your problem, and show us
> > what you have tried previously.
>
> Watch those versions :-)
>
> (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>
> jg

That would be an interesting title for a book. Take a somewhat simple
request and see how many different (or overly complex) solutions may
be generated for the request.

More specifically on your second point, regular expressions are not
available in Oracle 9i R2 - for some reason I thought that they were
introduced with Oracle 9i R1 (I even performed a search to verify - I
should have clicked one of the links). After seeing your post, I
searched again and found a couple interesting articles for those
people running Oracle 10g R1 and above:
http://download.oracle.com/owsf_2003/40105_Gennick_04.ppt
http://download.oracle.com/owsf_2003/40105.doc

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: Pankaj on 31 Dec 2009 13:58
On Dec 30, 4:49 pm, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> On Dec 30, 12:28 pm, joel garry <joel-ga...(a)home.com> wrote:
>
>
>
>
>
> > On Dec 30, 5:31 am, Charles Hooper <hooperc2...(a)yahoo.com> wrote:
> > LOL, you should write a book!  "Bad SQL! Bad, bad!"
>
> > > Something tells me you want to do it the easy way.  See if you can do
> > > anything with these functions:
> > > REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > > REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> > > *Always* post the DDL and DML to re-create your problem, and show us
> > > what you have tried previously.
>
> > Watch those versions :-)
>
> > (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>
> > jg
>
> That would be an interesting title for a book.  Take a somewhat simple
> request and see how many different (or overly complex) solutions may
> be generated for the request.
>
> More specifically on your second point, regular expressions are not
> available in Oracle 9i R2 - for some reason I thought that they were
> introduced with Oracle 9i R1 (I even performed a search to verify - I
> should have clicked one of the links).  After seeing your post, I
> searched again and found a couple interesting articles for those
> people running Oracle 10g R1 and above:http://download.oracle.com/owsf_2003/40105_Gennick_04.ppthttp://download.oracle.com/owsf_2003/40105.doc
>
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"http://hoopercharles.wordpress.com/
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thanks Everyone.

Carlos/Joe: I tried TRANSLATE option and it works.
Charles: I will go ahead with your option for now. Can you please
detail me on what the below expression is doing.

DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
(SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2

TIA.
From: Maxim Demenko on 31 Dec 2009 15:30
On 31.12.2009 19:58, Pankaj wrote:
> On Dec 30, 4:49 pm, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
>> On Dec 30, 12:28 pm, joel garry<joel-ga...(a)home.com> wrote:
>>
>>
>>
>>
>>
>>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...(a)yahoo.com> wrote:
>>> LOL, you should write a book! "Bad SQL! Bad, bad!"
>>
>>>> Something tells me you want to do it the easy way. See if you can do
>>>> anything with these functions:
>>>> REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>>
>>>> REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>>
>>>> *Always* post the DDL and DML to re-create your problem, and show us
>>>> what you have tried previously.
>>
>>> Watch those versions :-)
>>
>>> (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>>
>>> jg
>>
>> That would be an interesting title for a book. Take a somewhat simple
>> request and see how many different (or overly complex) solutions may
>> be generated for the request.
>>
>> More specifically on your second point, regular expressions are not
>> available in Oracle 9i R2 - for some reason I thought that they were
>> introduced with Oracle 9i R1 (I even performed a search to verify - I
>> should have clicked one of the links). After seeing your post, I
>> searched again and found a couple interesting articles for those
>> people running Oracle 10g R1 and above:http://download.oracle.com/owsf_2003/40105_Gennick_04.ppthttp://download.oracle.com/owsf_2003/40105.doc
>>
>> Charles Hooper
>> Co-author of "Expert Oracle Practices: Oracle Database Administration
>> from the Oak Table"http://hoopercharles.wordpress.com/
>> IT Manager/Oracle DBA
>> K&M Machine-Fabricating, Inc.- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks Everyone.
>
> Carlos/Joe: I tried TRANSLATE option and it works.
> Charles: I will go ahead with your option for now. Can you please
> detail me on what the below expression is doing.
>
> DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
> (SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
>
> TIA.

It checks, whether the second character in the column HOMEWORK
represents a digit. You can look at the results of the query
with t as (
select chr(32)||chr(rownum + 31) c from dual
connect by level <= 128-32
)
select c,
decode(sign(ascii(substr(c,2,1))-47),1,decode(sign(ascii
(substr(c,2,1))-58),-1,1,0),0) is_exc2
from t


Just to mention another approach regarding your question:

SQL> with t as (
2 select 'a12345' c from dual union all
3 select 'A123423' from dual union all
4 select 'g13452' from dual union all
5 select 'G452323' from dual union all
6 select 'h34423' from dual union all
7 select 'r34323' from dual union all
8 select 'b23232' from dual union all
9 select 'n' from dual union all
10 select 'n232323' from dual
11 )
12 -- End test data
13 select c
14 from t
15 where not lower(rtrim(c,'0123456789')) in ('h','b','n')
16 /

C
-------
a12345
A123423
g13452
G452323
r34323


Best regards

Maxim
From: Charles Hooper on 31 Dec 2009 16:04
On Dec 31, 1:58 pm, Pankaj <harpreet.n...(a)gmail.com> wrote:
> Thanks Everyone.
>
> Carlos/Joe: I tried TRANSLATE option and it works.
> Charles: I will go ahead with your option for now. Can you please
> detail me on what the below expression is doing.
>
> DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
> (SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
>

The numbers 0 through 9 have ASCII values ranging from 48 to 57.
* Obtain the second character in the column: SUBSTR(HOMEWORK,2,1)
* Use the ASCII function to find the ASCII value of the second
character
* Subtract 47 from the ASCII value for the second character
* If the difference is greater than 0, then:
** Subtract 58 from that ASCII value
** If the difference is less than 0, then we found an ASCII value
between 48 and 57 - therefore the second character must be a number
*** Return the number 1 if the ASCII value is between 48 and 57,
otherwise return 0

A CASE structure could be used rather than the cumbersome nested
DECODE and SIGN statements. A CASE structure will be easier to
maintain:
SELECT
CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48
AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1
ELSE 0 END IS_EXC2
FROM
T10;

You could transform this section to a CASE structure also:
DECODE(ASCII(SUBSTR(HOMEWORK,1,1)),104,1,72,1,66,1,98,1,78,1,110,1,0)
IS_EXC1

SELECT
CASE ASCII(SUBSTR(HOMEWORK,1,1))
WHEN 104 THEN 1
WHEN 72 THEN 1
WHEN 66 THEN 1
WHEN 98 THEN 1
WHEN 78 THEN 1
WHEN 110 THEN 1
ELSE 0 END IS_EXC1
FROM
T10;

Finally, you could combine the two CASE structures in the WHERE
clause:
SELECT
HOMEWORK,
ASCII(SUBSTR(HOMEWORK,1,1)) ASC_VAL1,
ASCII(SUBSTR(HOMEWORK,2,1)) ASC_VAL2
FROM
T10
WHERE
(CASE ASCII(SUBSTR(HOMEWORK,1,1))
WHEN 104 THEN 1
WHEN 72 THEN 1
WHEN 66 THEN 1
WHEN 98 THEN 1
WHEN 78 THEN 1
WHEN 110 THEN 1
ELSE 0 END) *
(CASE WHEN ASCII(SUBSTR(HOMEWORK,2,1)) >= 48
AND ASCII(SUBSTR(HOMEWORK,2,1)) <= 57 THEN 1
ELSE 0 END) = 0;

HOMEWORK ASC_VAL1 ASC_VAL2
---------- ---------- ----------
a12345 97 49
A123423 65 49
g13452 103 49
G452323 71 52
r34323 114 51
NB151517 78 66
C0151517 67 48
f9151517 102 57
HE4423 72 69

There are probably several other ways to solve this problem.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: Charles Hooper on 31 Dec 2009 16:14
On Dec 31, 3:30 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
> On 31.12.2009 19:58, Pankaj wrote:
>
>
>
>
>
> > On Dec 30, 4:49 pm, Charles Hooper<hooperc2...(a)yahoo.com>  wrote:
> >> On Dec 30, 12:28 pm, joel garry<joel-ga...(a)home.com>  wrote:
>
> >>> On Dec 30, 5:31 am, Charles Hooper<hooperc2...(a)yahoo.com>  wrote:
> >>> LOL, you should write a book!  "Bad SQL! Bad, bad!"
>
> >>>> Something tells me you want to do it the easy way.  See if you can do
> >>>> anything with these functions:
> >>>> REGEXP_INSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> >>>> REGEXP_SUBSTRhttp://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functi...
>
> >>>> *Always* post the DDL and DML to re-create your problem, and show us
> >>>> what you have tried previously.
>
> >>> Watch those versions :-)
>
> >>> (And thanks Carlos, I should've thought of that first. TIMTOWTDI)
>
> >>> jg
>
> >> That would be an interesting title for a book.  Take a somewhat simple
> >> request and see how many different (or overly complex) solutions may
> >> be generated for the request.
>
> >> More specifically on your second point, regular expressions are not
> >> available in Oracle 9i R2 - for some reason I thought that they were
> >> introduced with Oracle 9i R1 (I even performed a search to verify - I
> >> should have clicked one of the links).  After seeing your post, I
> >> searched again and found a couple interesting articles for those
> >> people running Oracle 10g R1 and above:http://download.oracle.com/owsf_2003/40105_Gennick_04.ppthttp://downl...
>
> >> Charles Hooper
> >> Co-author of "Expert Oracle Practices: Oracle Database Administration
> >> from the Oak Table"http://hoopercharles.wordpress.com/
> >> IT Manager/Oracle DBA
> >> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks Everyone.
>
> > Carlos/Joe: I tried TRANSLATE option and it works.
> > Charles: I will go ahead with your option for now. Can you please
> > detail me on what the below expression is doing.
>
> > DECODE(SIGN(ASCII(SUBSTR(HOMEWORK,2,1))-47),1,DECODE(SIGN(ASCII
> > (SUBSTR(HOMEWORK,2,1))-58),-1,1,0),0) IS_EXC2
>
> > TIA.
>
> It checks, whether the second character in the column HOMEWORK
> represents a digit. You can look at the results of the query
> with t as (
>   select chr(32)||chr(rownum + 31) c from dual
>   connect by level <= 128-32
> )
> select c,
> decode(sign(ascii(substr(c,2,1))-47),1,decode(sign(ascii
> (substr(c,2,1))-58),-1,1,0),0) is_exc2
> from t
>
> Just to mention another approach regarding your question:
>
> SQL> with t as (
>    2   select 'a12345' c from dual  union all
>    3   select 'A123423' from dual  union all
>    4   select 'g13452' from dual  union all
>    5   select 'G452323' from dual  union all
>    6   select 'h34423' from dual  union all
>    7   select 'r34323' from dual  union all
>    8   select 'b23232' from dual  union all
>    9   select 'n' from dual union all
>   10   select 'n232323' from dual
>   11  )
>   12  -- End test data
>   13  select c
>   14  from t
>   15  where not lower(rtrim(c,'0123456789')) in ('h','b','n')
>   16  /
>
> C
> -------
> a12345
> A123423
> g13452
> G452323
> r34323
>
> Best regards
>
> Maxim

Nice solution! I did not even think of using RTRIM to strip off the
characters at the right of the string when those characters are found
in the string. You did not even need to divide by 0 to produce the
desired result. :-)

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: oracle and database ebooks
Next: Oracle 10g Lite & .NET