OraFAQ Forum: SQL & PL/SQL » Identify the format and display the data type of a string

Home » SQL & PL/SQL » SQL & PL/SQL » Identify the format and display the data type of a string (Oracle DB 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Identify the format and display the data type of a string [message #681312]

Tue, 07 July 2020 06:42

adfnewbie
Messages: 54
Registered: January 2016

Member

Hi Experts,

I have a comma separated value in a DB column. The requirement is to split the values separated by comma and then display the data type of each value. Example is given below.

DB Column Value: 123.12,12-FEB-2020,abcde
Output expected: 123.12=NUMBER, 12-FEB-2020=DATE, abcde=VARCHAR

There can be any number of comma separated values in the column. The formats of the comma separated values can be:
123,123.01,12-FEB-2020,12-feb-2020,abcde,AABCDE,abCCDE

I have tried regular expression to split the values and the different values are displayed as different rows. Need assistance in achieving the required outcome. The query I tried is given below.

SELECT regexp_substr('123.12,12-FEB-2020,abcde','[^,]+', 1, level)
FROM dual
CONNECT BY regexp_substr('123.12, 12-FEB-2020, abcde', '[^,]+', 1, level) IS NOT NULL;

Any assistance is highly appreciated.
THANKS

Report message to a moderator

Identify the format and display the data type of a string [message #681313 is a reply to message #681312]

Tue, 07 July 2020 06:43

adfnewbie
Messages: 54
Registered: January 2016

Member

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681314 is a reply to message #681313]

Tue, 07 July 2020 07:32

EdStevens
Messages: 1375
Registered: September 2013

Senior Member

I don't know how to solve it, but let this be a lesson. You now see how and why this is an astoundingly bad design. NEVER put multiple values in a single columns. There's a reason data should be designed to Third Normal Form.

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681315 is a reply to message #681312]

Tue, 07 July 2020 07:38

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In your topic titled "Help with REGEXP_SUBSTR" Littlefoot gave you the query to split your string which is the one you use now.
In another topic you have been asked to format your post:

Michel Cadot wrote on Mon, 29 August 2016 18:14

From your previous topic:

Michel Cadot wrote on Tue, 07 June 2016 18:25

Quote:
So it's just about declaring and initialising and nothing else just like below?
Yes, please read [How to use [code] tags and make your code easier to read.
Your answer:

adfnewbie wrote on Tue, 07 June 2016 18:46
Thanks! It was a simple one so didn't bother to do the formatting! Will do all the time from now on!

Thanks again!

What is your actual issue here?
Find the type of the data? You have to define how to determine that.
Is '20200707' a number, a string or a date?

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681316 is a reply to message #681315]

Tue, 07 July 2020 07:46

adfnewbie
Messages: 54
Registered: January 2016

Member

The determining factor is the format of the value
--If it contains only numbers (123, 123.12), then its a NUMBER
--If it contains alphabets or alphanumeric (abcde, abc123), then its CHAR or VARCHAR
--If it contains date format like dd-mon-yyyy (12-JAN-2020 or 12-jan-2020), then its DATE

There cannot be any other formats.

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681317 is a reply to message #681316]

Tue, 07 July 2020 07:53

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

So +10, -12 or 1.0E3 is a string and not a number? And is 12-01-2020 a date? If so, how can you tell if it is Jan 12, 2020 or Dec 1 2020? You were told already - very bad design.

SY.

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681318 is a reply to message #681317]

Tue, 07 July 2020 07:55

adfnewbie
Messages: 54
Registered: January 2016

Member

The only possible formats for the values are as per my example values. The values +123, -12, 1.0E3, 12-01-2020 are not expected.

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681319 is a reply to message #681318]

Tue, 07 July 2020 08:29

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

WITH T AS (
           SELECT  '123,123.01,12-FEB-2020,12-feb-2020,abcde,AABCDE,abCCDE' STR FROM DUAL
          )
SELECT  ITEM,
        CASE
          WHEN REGEXP_LIKE(ITEM,'^\d+(\.\d+)?$') THEN 'NUMBER'
          WHEN TO_DATE(ITEM DEFAULT NULL ON CONVERSION ERROR,'DD-MON-YYYY','NLS_DATE_LANGUAGE = American') IS NOT NULL THEN 'DATE'
          ELSE 'VARCHAR2'
        END DATATYPE
  FROM  T,
        LATERAL(
                SELECT  REGEXP_SUBSTR(STR,'[^,]+',1,LEVEL) ITEM
                  FROM  DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT(STR,'[^,]+')
               )
/

ITEM                           DATATYPE
------------------------------ --------------------
123                            NUMBER
123.01                         NUMBER
12-FEB-2020                    DATE
12-feb-2020                    DATE
abcde                          VARCHAR2
AABCDE                         VARCHAR2
abCCDE                         VARCHAR2

7 rows selected.

SQL>

SY.

[Updated on: Tue, 07 July 2020 08:50]

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681320 is a reply to message #681319]

Tue, 07 July 2020 08:58

adfnewbie
Messages: 54
Registered: January 2016

Member

Thanks!
The one I tried is the below one. Is my query ok if we ignore the different date formats and assume that the only possible format is dd-mon-yyyy.

WITH dataset AS
(SELECT regexp_substr('123.12,ABCD,12-JAN-2020','[^,]+', 1, level) value FROM dual
CONNECT BY regexp_substr('123.12,ABCD,12-JAN-2020', '[^,]+', 1, level) IS NOT NULL
)
SELECT value,
CASE
WHEN regexp_like (value,':digit:{2}-:upper:{3}-:digit:{4}') THEN 'DATE'
WHEN regexp_like (value,':upper:') THEN 'STRING'
WHEN regexp_like (value,':digit:') THEN 'NUMBER'
ELSE 'UNKNOWN'
END DATATYPE
FROM dataset;

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681321 is a reply to message #681319]

Tue, 07 July 2020 08:59

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Ah, I missed you are on 11G and DEFAULT ON CONVERSION ERROR was introduced in 12.2. Then you need to write IS_DATE function:

CREATE OR REPLACE
  FUNCTION IS_DATE(
                   P_STR    VARCHAR2,
                   P_FORMAT VARCHAR2,
                   P_NLS    VARCHAR2
                  )
    RETURN NUMBER
    DETERMINISTIC
    IS
        V_DATE DATE;
    BEGIN
        V_DATE := TO_DATE(P_STR,P_FORMAT,P_NLS);
        RETURN 1;
      EXCEPTION
        WHEN OTHERS
          THEN
            RETURN 0;
END;
/
WITH T AS (
           SELECT  '123,123.01,12-FEB-2020,12-feb-2020,abcde,AABCDE,abCCDE' STR FROM DUAL
          )
SELECT  ITEM,
        CASE
          WHEN REGEXP_LIKE(ITEM,'^\d+(\.\d+)?$') THEN 'NUMBER'
          WHEN IS_DATE(ITEM,'DD-MON-YYYY','NLS_DATE_LANGUAGE = American') = 1 THEN 'DATE'
          ELSE 'VARCHAR2'
        END DATATYPE
  FROM  T,
        LATERAL(
                SELECT  REGEXP_SUBSTR(STR,'[^,]+',1,LEVEL) ITEM
                  FROM  DUAL
                  CONNECT BY LEVEL <= REGEXP_COUNT(STR,'[^,]+')
               )
/

ITEM                           DATATYPE
------------------------------ --------------------
123                            NUMBER
123.01                         NUMBER
12-FEB-2020                    DATE
12-feb-2020                    DATE
abcde                          VARCHAR2
AABCDE                         VARCHAR2
abCCDE                         VARCHAR2

7 rows selected.

SQL>

SY.

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681322 is a reply to message #681320]

Tue, 07 July 2020 09:51

Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

adfnewbie wrote on Tue, 07 July 2020 15:58

Thanks!
The one I tried is the below one. Is my query ok if we ignore the different date formats and assume that the only possible format is dd-mon-yyyy.

WITH dataset AS
(SELECT regexp_substr('123.12,ABCD,12-JAN-2020','[^,]+', 1, level) value FROM dual
CONNECT BY regexp_substr('123.12,ABCD,12-JAN-2020', '[^,]+', 1, level) IS NOT NULL
)
SELECT value,
CASE
WHEN regexp_like (value,':digit:{2}-:upper:{3}-:digit:{4}') THEN 'DATE'
WHEN regexp_like (value,':upper:') THEN 'STRING'
WHEN regexp_like (value,':digit:') THEN 'NUMBER'
ELSE 'UNKNOWN'
END DATATYPE
FROM dataset;

Once more:

Michel Cadot wrote on Tue, 07 July 2020 14:38

...In another topic you have been asked to format your post:

Michel Cadot wrote on Mon, 29 August 2016 18:14

From your previous topic:

Michel Cadot wrote on Tue, 07 June 2016 18:25

Quote:
So it's just about declaring and initialising and nothing else just like below?
Yes, please read How to use [code] tags and make your code easier to read.

Your answer:

adfnewbie wrote on Tue, 07 June 2016 18:46
Thanks! It was a simple one so didn't bother to do the formatting! Will do all the time from now on!

Thanks again!
...