Find data where last two characters are text 1

[ekta22]

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

 

[SantaMufasa]

Ekta,

The following WHERE clause should produce the results you want

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.

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

 

[SantaMufasa]

Find data where last two characters are text

I presume, Ekta, that by "text" you mean "alphabetic", is that correct?

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

 

[Beilstwh]

and showing that there is always another way to do something, how about

>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]

Bill,

B0 and Z9 colate after AA and before ZZ.
[tt]
AA
B0
Z9
ZZ
[/tt]

Skip,

_[red][/red]

 

[SkipVought]

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

>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,

_[red][/red]

 

[Beilstwh]

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]

Dave,

Wonderful solution and detailed explanation! I learned something!

Thanx! ==> *

Skip,

_[red][/red]

 

[ekta22]

Thanks guys! Works great.

 

[ekta22]

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]

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>

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

 

[ekta22]

yea true..but then why am I seeing 'BBBBV2' in the results where 2 is not between 'AA' and 'ZZ'.

 

[SantaMufasa]

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?



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

 

[ekta22]

gotcha.. makes sense now..
thanks for explaining it so well!