Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

ekta22 (IS/IT--Management)
8 Jun 07 15:15
Hi,

I have need to filter the data in my table based on a field where the length is 6 and the last two characters are text.

For example if I have the following data as below in my column my query should pick only 1001AG and 100AAG

100103 - last two characters are number
10002A - second last characters is a number
10003 - lenght is not 6 and last two characters are number.
1001AG - should be picked..lenght is 6 and last two characters are text
100AAG - should be picked..lenght is 6 and last two characters are text
021BV - length is not 6

Is this possible to do so?

Appreciate ant help.

Thanks

-E
Helpful Member!  SantaMufasa (TechnicalUser)
8 Jun 07 15:44
Ekta,

The following WHERE clause should produce the results you want

CODE

SQL> select * from ekta;

TXT
----------
100103
10002A
10003
1001AG
100AAG
021BV

6 rows selected.

select txt from ekta
 where length(txt)=6
   and nvl(length(translate(substr(txt,-2)
           ,'%ABCDEFGHIJKLMNOPQRSTUVWXYZ','%')),'0') = 0
/

TXT
------
1001AG
100AAG

2 rows selected.
Here is an explanation:

1) length(txt)=6: Allows only strings of length 6
2) nvl(length(translate(substr(txt,-2),'%ABCDEFGHIJKLMNOPQRSTUVWXYZ','%')),'0') = 0:
   Proceeding from innermost parens:
   a) substr(txt,-2): obtain just the last two characters from the string.
   b) translate(...: of those 2 characters, translate any A-Z characters to NULL.
   c) length(...: determine the length of the translated string.
   d) nvl(...: if the length was NULL, use "0" instead...
   e) = 0: ...to compare to "0".
   f) if the comparison is TRUE, then display the row.

Let us know if this is what you wanted.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

SantaMufasa (TechnicalUser)
8 Jun 07 15:46

Quote (Ekta):

Find data where last two characters are text
I presume, Ekta, that by "text" you mean "alphabetic", is that correct?

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

Beilstwh (Programmer)
9 Jun 07 10:41
and showing that there is always another way to do something, how about

CODE

>select * from ekta;

TXT
--------------------
100103
100002A
10003
1001AG
100AAG
021BV

6 rows selected.

>select txt from ekta
  2   where length(txt)=6
  3     and upper(substr(txt,-2)) between 'AA' AND 'ZZ';

TXT
--------------------
1001AG
100AAG

Bill
Oracle DBA/Developer
New York State, USA

SkipVought (Programmer)
9 Jun 07 10:51



Bill,

B0 and  Z9 colate after AA and before ZZ.

AA
B0
Z9
ZZ

Skip,

glasses
tongue

SkipVought (Programmer)
9 Jun 07 10:55




I would have done it this way, not as elegantly as SM..

CODE

>select txt from ekta
  2   where length(txt)=6
  3     and upper(substr(txt,5,1)) between 'A' AND 'Z'
  4     and upper(substr(txt,6,1)) between 'A' AND 'Z';

Skip,

glasses
tongue

Beilstwh (Programmer)
9 Jun 07 10:58
Your right, what an idiot I am it should have been


select txt from ekta
 where length(txt)=6
 and upper(substr(txt,-2,1)) between 'A' AND 'Z'
 and upper(substr(txt,-1,1)) between 'A' AND 'Z';

Bill
Oracle DBA/Developer
New York State, USA

SkipVought (Programmer)
9 Jun 07 11:09



Dave,

Wonderful solution and detailed explanation!  I learned something!  

Thanx!  ==> *

Skip,

glasses
tongue

ekta22 (IS/IT--Management)
9 Jun 07 14:17
Thanks guys! Works great.
ekta22 (IS/IT--Management)
13 Jul 07 17:28
Hey guys,

I posted this thread a little while ago. At the time it was my bad that I din't check the solution thoroughly. But I recently realized that it was not working right. It was bringing in records such as
BBBBV2
AAAAX1

The code that I used from the thread above was -

select txt from ekta
where length(txt)=6
and upper(substr(txt,-2)) between 'AA' AND 'ZZ';


This is what I used to make it work
SELECT txt FROM ekta
WHERE LENGTH(TRIM(TXT)) = 6
AND REGEXP_INSTR(SUBSTR(TRIM(TXT), 5, 6), '[A-Z][A-Z]') = 1;

But I am just wondering why the initial solution didn't work.

Thanks!



SantaMufasa (TechnicalUser)
13 Jul 07 17:42

Quote (Ekta):

But I am just wondering why the initial solution didn't work.
Because aren't "V<any character>" and "X<any character>" between "AA" and "ZZ"? <smile>

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

ekta22 (IS/IT--Management)
16 Jul 07 9:02
yea true..but then why am I seeing 'BBBBV2' in the results where 2 is not between 'AA' and 'ZZ'.
SantaMufasa (TechnicalUser)
16 Jul 07 13:42
Ekta,

Let's try to explain it this way:

1) Is "V" between "A" and "Z"? Yes.
2) Is "Va" between "A" and "Z"? Yes.
3) Is "V1" between "A" and "Z"? Yes.
4) Is "V2" between "A" and "Z"? Yes.

an by extension:

5) Is "V" between "AA" and "ZZ"? Yes.
6) Is "Va" between "AA" and "ZZ"? Yes.
7) Is "V1" between "AA" and "ZZ"? Yes.
8) Is "V2" between "AA" and "ZZ"? Yes.

The point is...It really doesn't matter what the second character is in the above comparisons since the first character, ALONE, places the string BETWEEN the boundaries. You are getting fooled/lulled into a false sense of logic by thinking that Oracle somehow compares the second characters independently of the first characters. Oracle only looks at the second characters if there is somehow a TIE when looking at the first characters.

This logic fallacy is why all of the successful comparison methods, above, split apart the comparisons of the first characters from the second characters.

Does this help explain things satisfactorily?

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

ekta22 (IS/IT--Management)
17 Jul 07 9:09
gotcha.. makes sense now..smile
thanks for explaining it so well!

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close