postgresqlpostgresql-9.5

How to trim first '/' after filename in postgres


I need small help, Below is query through which I am fetching the data after filename and I am getting it but there is scenario where in some cases we don't have '/' after filename so I am planning to trim '/' that is coming after filename.Can anyone please help. I have provided query with input,current output and expected output :

Input :

select lower(substring(split_part('ksh $sample/wrapper.sh --filename /abc/test/svc_test_abc_card.yaml', 'filename', 2) from '[^ ]+'::text))

Current Output

/abc/test/svc_test_abc_card.yaml

expected output:

abc/test/svc_test_abc_card.yaml

Solution

  • In one of your previous questions you did already use ltrim(), which should be enough to do the job here too. Demo at db<>fiddle:

    with your_table(example) as (values
      ('ksh $sample/wrapper.sh --filename /abc/test/svc_test_abc_card.yaml'))
    select ltrim(
             lower(
               substring(
                 split_part( example
                            ,'filename'
                            ,2) 
                 from '[^ ]+'::text))
            ,'/')
    from your_table;
    
    ltrim
    abc/test/svc_test_abc_card.yaml

    But as @Stefanov.sm pointed out, what you're doing with split_part() can be handled directly in the regular expression, and that also goes for the ltrim():

    with your_table(example) as (values
      ('ksh $sample/wrapper.sh --filename /abc/test/svc_test_abc_card.yaml'))
    select lower(substring(example from 'filename\s+/?([^ ]+)'))
    from your_table;
    
    substring
    abc/test/svc_test_abc_card.yaml