Home » SQL & PL/SQL » SQL & PL/SQL » SUBSTR with just numeric characters
SUBSTR with just numeric characters [message #311122] Thu, 03 April 2008 08:18 Go to next message
brandointheweb
Messages: 25
Registered: August 2005
Location: Germany
Junior Member
Hi all,
following sql statement gives me a substring of a CLOB-field:
....TO_CHAR(SUBSTR(T.DATEIINHALT,311,3)) AS ANZAHL_PAL
Results Are:
35:Z
5:ZM
123
7:Z
Now my question:
How Can I reach the aim that only the numbers are listed?
For my example:
35
123
7

Thanks for Your help.
Re: SUBSTR with just numeric characters [message #311125 is a reply to message #311122] Thu, 03 April 2008 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>How Can I reach the aim that only the numbers are listed?
By using either TRANSLATE or REPLACE function
Re: SUBSTR with just numeric characters [message #311203 is a reply to message #311125] Thu, 03 April 2008 12:48 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Regular expressions (if your database version supports them) might also help; for example
SQL> select col, regexp_substr(col, '[[:digit:]]+') result from test;

COL                  RESULT
-------------------- --------------------
35:Z                 35
5:ZM                 5
123                  123
7:Z                  7

SQL>
Previous Topic: help with select statement
Next Topic: Query rewrite problem related to FGAC/RLS/VPD
Goto Forum:
  


Current Time: Fri Dec 09 11:40:17 CST 2016

Total time taken to generate the page: 0.12608 seconds