Home » SQL & PL/SQL » SQL & PL/SQL » Applying function on column having multiple datatypes
Applying function on column having multiple datatypes [message #665111] Tue, 22 August 2017 03:05 Go to next message
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 #665112 is a reply to message #665111] Tue, 22 August 2017 03:26 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Not tested, but something like this should do:

select case when trim(TRANSLATE(col1, '0123456789-,.', ' ')) is null
            then round(to_number(col1)) || 'x' -- numeric
            else col1                          -- alpha
       end
from tab1;
Re: Applying function on column having multiple datatypes [message #665115 is a reply to message #665112] Tue, 22 August 2017 03:46 Go to previous messageGo to next message
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

Re: Applying function on column having multiple datatypes [message #665117 is a reply to message #665115] Tue, 22 August 2017 04:10 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd say it's for more likely that the round is getting applied to the char data and that's throwing the error, rather than what you suggested.
Re: Applying function on column having multiple datatypes [message #665120 is a reply to message #665117] Tue, 22 August 2017 04:45 Go to previous messageGo to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
Yes exactly. And column contains both type of data. The requirement was to round numeric data from the column and do nothing to character data. Using case expression suggested by frank worked Smile

Thanks for your response.
Re: Applying function on column having multiple datatypes [message #665121 is a reply to message #665112] Tue, 22 August 2017 04:50 Go to previous messageGo to next message
oracbeg
Messages: 16
Registered: July 2017
Junior Member
Hi Frank. Thank You for your response. The case expression worked. The function i used here is "regexp_like" making all over expression as follows.

select case when regexp_like(col, '\d+(\.\d+)?') 
          then to_char(round(to_number(col), 2)) || 'x'
          else col
       end
from  table;
Re: Applying function on column having multiple datatypes [message #665125 is a reply to message #665111] Tue, 22 August 2017 07:51 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
This begs the question of why do you have different datatypes in a column? This is just going to lead to more problem in the future. It is just going to get worse and worse.
Previous Topic: Using Trigger in Oracle DB to update a table in SQL Server DB
Next Topic: Wanted to delete duplicate records
Goto Forum:
  


Current Time: Fri Apr 19 15:38:08 CDT 2024