| Query for selecting maximum value from character field [message #371571] |
Wed, 08 November 2000 23:20  |
Pravin Kulkarni
Messages: 2
Registered: November 2000
|
Junior Member |
|
|
|
I am facing one problem. I had a table, which contains a column with character data type. It consists of Numeric values as well as Character values. I want Maximum of Numeric values using only one SQL statement.I know that it is possible using PL/SQL.Is it possible using one SQL statement?
|
|
|
|
| Re: Query for selecting maximum value from character field [message #371573 is a reply to message #371571] |
Thu, 09 November 2000 03:54  |
John R
Messages: 156
Registered: March 2000
|
Senior Member |
|
|
SELECT MAX(NVL(
REPLACE(
TRANSLATE(UPPER(Field)
,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
,' (spaces as long as the quoted string)'
),' ',''
)
),0)
FROM Table
This translates all non-numeric characters into spaces (yo may want to add punctuation chrs to the list) and then truncates all spaces, leaving you with a numeric
field.
(Sorry about the formatting, but this isn't th most friendly editor ever)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
