Applying function on column having multiple datatypes [message #665111] |
Tue, 22 August 2017 03:05 |
|
oracbeg
Messages: 16 Registered: July 2017
|
Junior Member |
|
|
Applying round function in select statement to only numeric values and leaving character values as it is in a single column in oracle.
I need to use round function or any other function on a column in oracle table having both character values and numeric decimal values. It should make changes to numeric values only while retrieval and should retrieve character/string values as it is.
i tried but it says invalid number.
for eg:
column(in table)(1.7313883, 1.7313883
1.63721,nm,nm, nm)
note: it should append a character(x) with numeric values and nothing with character values.
after retrieval reqd:
column/required(1.73x, 1.73x, 1.63x, nm, nm, nm)
|
|
|
|
Re: Applying function on column having multiple datatypes [message #665115 is a reply to message #665112] |
Tue, 22 August 2017 03:46 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
Probably your NLS_NUMERIC_CHARACTERS is not set to '.'. Meaning the decimal character is not '.'. You could try to explicitly convert it to ','
WITH
TESTDATA
AS
(SELECT '1.7313883' AS VALS FROM DUAL
UNION ALL
SELECT '1.7313883' FROM DUAL
UNION ALL
SELECT '1.63721' FROM DUAL
UNION ALL
SELECT 'nm' FROM DUAL
UNION ALL
SELECT 'nm' FROM DUAL
UNION ALL
SELECT 'nm' FROM DUAL)
SELECT CASE WHEN
REGEXP_LIKE(VALS, '([:digit:]|\.)') THEN
TO_CHAR(
ROUND(
TO_NUMBER(
REGEXP_REPLACE(VALS, '\.', ',') -- replacement takes place here
), 2
)
) || 'x'
ELSE VALS
END
DAT
FROM TESTDATA;
[Updated on: Tue, 22 August 2017 03:50] Report message to a moderator
|
|
|
|
|
|
|