How do i extract the date from filename through sql-CodePudding

My file name is like below

dbfs:/mnt/edpraw/raw_daily_flash/raw_daily_flash_eu/partition_ts=20221010151556/eu_dsf_20221010050123_2022101004.csv

I want to get the output like 20221010050123

CodePudding user response：

Not sure what SQL you are using but many do support split_part so your query would look like this:

SELECT split_part(split_part('dbfs:/mnt/edpraw/raw_daily_flash/raw_daily_flash_eu/partition_ts=20221010151556/eu_dsf_20221010050123_2022101004.csv', '/',7), '_', 3);

CodePudding user response：

you can do it dynamically without passing indexes:

    declare @path nvarchar(MAX) = 'dbfs:/mnt/edpraw/raw_daily_flash/raw_daily_flash_eu/partition_ts=20221010151556/eu_dsf_20221010050123_2022101004.csv'
    declare @time nvarchar(MAX)
    
    --first extract before last '_'
    set @time = left(@path, len(@path) - charindex('_', reverse(@path)   '_'))
    print @time

    --then extract after last '_'
    set @time = right(@time, charindex('_', reverse(@time)   '_') - 1)
    print @time

Note this will work no matter change in length of path, but format.