Home » SQL & PL/SQL » SQL & PL/SQL » Identify the format and display the data type of a string (Oracle DB 11g)
Identify the format and display the data type of a string [message #681312] Tue, 07 July 2020 06:42 Go to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
EdStevens
Messages: 1376
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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?

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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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 Go to previous messageGo to next message
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;
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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!
...
You still do not bother to format, this is having no regard for us.

[Updated on: Tue, 07 July 2020 09:53]

Report message to a moderator

Re: Identify the format and display the data type of a string [message #681323 is a reply to message #681322] Tue, 07 July 2020 09:58 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
I apologize for the format.

I formatted in SQL Developer and pasted it here.
After posting the message, it went haywire.
Re: Identify the format and display the data type of a string [message #681324 is a reply to message #681323] Tue, 07 July 2020 11:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So click on the link and you will know how to do it.

Previous Topic: To store table row count in the same table's comment
Next Topic: Running script present on webserver
Goto Forum:
  


Current Time: Thu Mar 28 15:17:08 CDT 2024