Extracting the Date Based on a pattern

ozarkram

Hi All,

I am trying to extract the date from one column based on a pattern..For example in the table below

if the column orderstatus has a * in the beginning and then there is a date of the format mm/dd/yyyy then I need to extract that date as the soldorstockdate otherwise I need to extract the date from orddate as the soldorstockdate...Can you please let me know how to do this...

Thanks,

Arun

Godiepi

Yes no problem , no need to say WHEN again ... This way should clear out the error

CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'

 THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

      ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

 end

Godiepi

Hi @ozarkram

if always the date you need is present at the end of the string and the identifier is * then first check for that asterisk character present, then get the last 10 of the string converting it to date format mm/dd/yyyy else use the column orddate

this beastmode should do the job

 -- Name your beastmode Soldorstockdate
CASE
    WHEN INSTR(`orderstatus`, '*') >= 1
    THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
    ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    END

Godiepi

@ozarkram

1. check for the * character in the string
2. if present then get the last 10 characters from the strings (if always is the date at the end)
3. convert it to a date format month , day and year
4. if the character * is not present then use the other column

This beastmode should do the job

-- name your beastmode Soldorstockdate
CASE
    WHEN INSTR(`orderstatus`, '*') >= 1
       THEN DATE_FORMAT(RIGHT(TRIM(`orderstatus`),10),'%m/%d/%Y')
       ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')
    END

ozarkram

Hi @Godiepi ...Really appreciate you getting back!..Can you please let me know what is the error with below code:

CASE WHEN LEFT(`ORDERSTATUS`,1)='*' OR

 WHEN `ORDERSTATUS` like '%BAM%'

 THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

      ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

 end

Godiepi

Yes no problem , no need to say WHEN again ... This way should clear out the error

CASE WHEN LEFT(`ORDERSTATUS`,1) ='*' OR `ORDERSTATUS` like '%BAM%'

 THEN DATE_FORMAT(RIGHT(TRIM(`ORDERSTATUS`),10),'%m/%d/%Y')

      ELSE DATE_FORMAT(`orddate`,'%m/%d/%Y')

 end