Home » SQL & PL/SQL » SQL & PL/SQL » Sql query (Oracle 10g)
Sql query [message #568712] Tue, 16 October 2012 01:40 Go to next message
subhash.pophale
Messages: 9
Registered: December 2007
Location: Mumbai
Junior Member
Hello,

I have a column COL1 in table TAB1 which is varchar2.
I want select only rows which has number and not alphanumeric value?
I don't want to use regexp for this since it would be bit complex for support guys.
Any suggestions?

Thanks.

[Updated on: Tue, 16 October 2012 01:41]

Report message to a moderator

Re: Sql query [message #568722 is a reply to message #568712] Tue, 16 October 2012 02:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use TRANSLATE... but I don't think it would be easier for support guys it they don't understand regexp.

With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel

[Updated on: Tue, 16 October 2012 02:04]

Report message to a moderator

Re: Sql query [message #568723 is a reply to message #568722] Tue, 16 October 2012 02:05 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Use can use TO_NUMBER conversion..
Re: Sql query [message #568724 is a reply to message #568723] Tue, 16 October 2012 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not possible... or show me I am wrong (with no PL/SQL).

Regards
Michel
Re: Sql query [message #568725 is a reply to message #568712] Tue, 16 October 2012 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming "number" means value with only figures:
SQL> with
  2    data as (
  3      select 'abc543jhk' val from dual
  4      union all
  5      select '123' val from dual
  6    )
  7  select val
  8  from data
  9  where translate(val, 'A0123456789', 'A') is null
 10  /
VAL
---------
123

Regards
Michel

Re: Sql query [message #568730 is a reply to message #568712] Tue, 16 October 2012 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
subhash.pophale wrote on Tue, 16 October 2012 08:40
I don't want to use regexp for this since it would be bit complex for support guys.

Brilliant! ./fa/2115/0/
Re: Sql query [message #568734 is a reply to message #568712] Tue, 16 October 2012 03:04 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
subhash.pophale wrote on Tue, 16 October 2012 08:40

I have a column COL1 in table TAB1 which is varchar2.
I want select only rows which has number and not alphanumeric value


Hi there,


Try this

WITH testascii AS
(
  SELECT 'aaaaaa'   AS colval FROM DUAL UNION
  SELECT 'aaa111'   AS colval FROM DUAL UNION
  SELECT '225'      AS colval FROM DUAL UNION
  SELECT 'B'        AS colval FROM DUAL UNION
  SELECT 'a1b2ffgs' AS colval FROM DUAL UNION
  SELECT '17'       AS colval FROM DUAL UNION
  SELECT '98710'    AS colval FROM DUAL
)
SELECT colval
FROM testascii t1
WHERE NOT EXISTS 
(
  SELECT t2.colval
  FROM testascii t2
  WHERE (t1.colval = t2.colval) AND
  EXISTS
    (
      SELECT ASCII(SUBSTR(t3.colval, LEVEL, 1))
      FROM testascii t3
      WHERE (t2.colval = t3.colval) AND
            (ASCII(SUBSTR(t3.colval, LEVEL, 1)) NOT BETWEEN 48 AND 57)
      CONNECT BY LEVEL <= LENGTH(t3.colval)
    )
);



COLVAL
--------
225
17
98710

SQL> 



Regards,
Dariyoosh
Re: Sql query [message #568735 is a reply to message #568734] Tue, 16 October 2012 03:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Far more complex than my simple:
SQL> WITH testascii AS
  2  (
  3    SELECT 'aaaaaa'   AS colval FROM DUAL UNION
  4    SELECT 'aaa111'   AS colval FROM DUAL UNION
  5    SELECT '225'      AS colval FROM DUAL UNION
  6    SELECT 'B'        AS colval FROM DUAL UNION
  7    SELECT 'a1b2ffgs' AS colval FROM DUAL UNION
  8    SELECT '17'       AS colval FROM DUAL UNION
  9    SELECT '98710'    AS colval FROM DUAL
 10  )
 11  SELECT colval
 12  FROM testascii 
 13  where translate(colval, 'A0123456789', 'A') is null
 14  /
COLVAL
--------
17
225
98710

And I don't think a support guy that does not understand regexp will understand your query (I didn't even try to understand it). Wink

Regards
Michel

[Updated on: Tue, 16 October 2012 03:10]

Report message to a moderator

Re: Sql query [message #568736 is a reply to message #568735] Tue, 16 October 2012 03:18 Go to previous messageGo to next message
Littlefoot
Messages: 19648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How is this REGEXP_LIKE "complex"? Query that Dariyoosh posted is far more complex, in my opinion.
SQL> WITH testascii AS
  2  (
  3    SELECT 'aaaaaa'   AS colval FROM DUAL UNION
  4    SELECT 'aaa111'   AS colval FROM DUAL UNION
  5    SELECT '225'      AS colval FROM DUAL UNION
  6    SELECT 'B'        AS colval FROM DUAL UNION
  7    SELECT 'a1b2ffgs' AS colval FROM DUAL UNION
  8    SELECT '17'       AS colval FROM DUAL UNION
  9    SELECT '98710'    AS colval FROM DUAL
 10  )
 11  select colval
 12  from testascii
 13  where regexp_like(colval, '^\d+$');

COLVAL
--------
17
225
98710

SQL>
Re: Sql query [message #568738 is a reply to message #568735] Tue, 16 October 2012 03:28 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
Michel Cadot wrote on Tue, 16 October 2012 10:08

And I don't think a support guy that does not understand regexp will understand your query

Smile I think you're right about that

Michel Cadot wrote on Tue, 16 October 2012 10:08

(I didn't even try to understand it). Wink

Bad strings are those that contain at last one letter, the inner most subquery (having alias t3) which uses CONNECT BY LEVEL) splits each string into several rows, one character per row. Then you check the ASCII code of each character to see whether it is in the range of digits. The second subquery (with table alias t2) verifes that whether such a bad string exists. And the main SELECT statement (table alias t1) simply excludes undesired elements from the result.

But of course, your solution is far better Wink and now that I look at the documentation of TRANSLATE, it is precisely what oracle suggests:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions216.htm#i1501659
Quote:

...
To remove all characters in from_string, concatenate another character to the beginning of from_string and specify this character as the to_string. For example, TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr.
...



Conclusion : You won ! Smile



Regards,
Dariyoosh

Re: Sql query [message #568740 is a reply to message #568738] Tue, 16 October 2012 03:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59289
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ah! the solution was in the documentation itself. Laughing

Regards
Michel
Re: Sql query [message #568743 is a reply to message #568740] Tue, 16 October 2012 04:46 Go to previous message
subhash.pophale
Messages: 9
Registered: December 2007
Location: Mumbai
Junior Member
Thanks..I will use TRANSLATE
Previous Topic: pre requisit question
Next Topic: BFILENAME issue
Goto Forum:
  


Current Time: Wed Oct 01 07:21:37 CDT 2014

Total time taken to generate the page: 0.06087 seconds