Extracting the Date Based on a pattern

ozarkram · 2022-05-26T22:09:55+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Beast Mode

Accepted answers

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

All comments

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

check for the * character in the string
if present then get the last 10 characters from the strings (if always is the date at the end)
convert it to a date format month , day and year
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