Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:

case is_date and is_string functions in Oracle

I'm trying to come up with a way to check if a field that I'm selecting from a database is a date or a string that I can do within a case statement.  here's part of the select:

select  /*+ full(i) */
       decode(upper(i.TYPE), 'NEW', :NEW_BASE_NAME, :USED_BASE_NAME) BASE_NAME,
       i.context_guid,
       o.OWNER_GUID,
       substr(o.IA_DEALER_ID, 3)                  DEALER_ID,
       STOCK_NUM,
       to_char(i.INVENTORY_DATE, 'MM/DD/YYYY')    INVENTORY_DATE,      
      i.VEHICLE.SUB_TRIM_LEVEL                   V_SUB_TRIM_LEVEL,

the problem is that i.INVENTORY_DATE is sometimes returning an integer (1, 300) and it is not an actual date.  i.VEHICLE.SUB_TRIM_LEVEL is sometimes coming back as a date, rather than a string.  I want to put case statements around these two fields, so I can make sure that i.INVENTORY_DATE is coming out as a date, and if not, return NULL, and if i.VEHICLE.SUB_TRIM_LEVEL is coming out as a date and not a string, then return NULL.  how can I accomplish this with a case statement?  I'm assuming it is not going to be very simple, since there are no is_date or is_string functions in Oracle.  thanks!

0
texasreddog
Asked:
texasreddog
  • 3
  • 2
1 Solution
 
sdstuberCommented:
what are the actual datatypes of those columns?

if i.VEHICLE.SUB_TRIM_LEVEL is a string  then a string that happens to be human readable as a date is still a string.  Is there a particular string or format you would want to detect and NULL?

as for the inventory_date,  if it's an integer sometimes and a date others, then I assume it's really just a string.   If so, then reformatting it with to_char won't help.  If you want to assume all strings that look like xx/xx/xxxx  are dates, then try this...

         CASE
             WHEN REGEXP_INSTR(i.inventory_date, '^[0-9]{2,2}/[0-9]{2,2}/[0-9]{4,4}$') > 0
             THEN
                 i.inventory_date
             ELSE
                 NULL
         END
             inventory_date

if you're looking for something similar in sub_trim_level then it would be the reverse...

        CASE
             WHEN REGEXP_INSTR(i.i.vehicle.sub_trim_level, '^[0-9]{2,2}/[0-9]{2,2}/[0-9]{4,4}$') =
                      0
             THEN
                 i.i.vehicle.sub_trim_level
             ELSE
                 NULL
         END
             v_sub_trim_level

0
 
texasreddogAuthor Commented:
ok, the regexp_instr statement seems to work, but I need to add another case to one of these statements.  I tried this:

case when regexp_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^[0-9]{2,2}/[0-9]{2,2}/[0-9]{4,4}$') = 0
       then i.VEHICLE.SUB_TRIM_LEVEL
       when regexp_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^[0-9]{1,1}') = 0
       then i.VEHICLE.SUB_TRIM_LEVEL
       when is_number(i.VEHICLE.SUB_TRIM_LEVEL) = 'N'
       then i.VEHICLE.SUB_TRIM_LEVEL
       else NULL end                              V_SUB_TRIM_LEVEL

I tested by inserting a '10' into i.vehicle.sub_trim_level.  the first part of the case statement seems to work, but it is still displaying a 10 when I select it from the table.  thus it doesn't look like it is not checking the second or third conditions in this case.  how do I make it check all three cases, so I will for sure only get a string back that begins with a letter?  '10' should show 'Y' for is_number and also 1 for the second regular expression, since the first character is a 1.
0
 
sdstuberCommented:
'10'  matches the first condition...

the first looks for xx/xx/xxxx  where x is a digit.  If it does not find that pattern then return i. vehicle_sub_trim_level.

'10' is not that pattern.  so it gets returned.


what are the rest of your conditions trying to do?  the first 3 all return the same result,  I would write them as a single condition with "OR" instead of 3 separate conditions.

right now what they do is:

2nd condition-  if the first character is not a digit then return the string

3rd condition - if the "is_number" function returns N then return the string.

for all else return null.

not sure why you need the 2nd condition,  the 3rd condition would satisfy that.


what is "is_number"  ?  I can guess what it is supposed to do,  does it work?




0
 
texasreddogAuthor Commented:
the is_number condition just checks to see if the data is a number.  I see what you are saying.  I'm going to make the conditions together, but use and instead of or, to make it more strict.  I think using and will work.
0
 
sdstuberCommented:
you can't use AND with the conditions, it will have to be OR

reason is,  a string can't be both a number AND of the format xx/xx/xxxx

it can only be one OR the other, or neither

if your conditions are it should be a date or a number then try the first example below

note, by "number" I assume you mean integer as well as decimal and postivve or negative

if you mean number to mean positive integer only, then try the second one...
case when regexp_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^[0-9]{2,2}/[0-9]{2,2}/[0-9]{4,4}$') = 0
       or REGEXP_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^(\+|\-)?[0-9]*\.?[0-9]*$') = 1
     then i.VEHICLE.SUB_TRIM_LEVEL
     else NULL 
end 
 
 
case when regexp_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^[0-9]{2,2}/[0-9]{2,2}/[0-9]{4,4}$') = 0
       or REGEXP_instr(i.VEHICLE.SUB_TRIM_LEVEL, '^[0-9]+$') = 1
     then i.VEHICLE.SUB_TRIM_LEVEL
     else NULL 
end 

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now