#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0

    Postgresql reverse string with backslash?


    Greetings. I'm trying to write a function to remove the \path\to\a\file.jpg so all i'm left with is the file.jpg

    My strategy is to reverse the test, look for the first backslash, then kill that backslash and everything after it. then reverse again.

    But my new function fails. When i step through it with a debugger it SEEMS to work but then when i run it no go.

    what am i doing wrong?!! I've broken the function down to simple components to try and debug it. I'm not even doing the path-stripping part yet since i can't get the reverse to work with the backslashes....

    Code:
    CREATE OR REPLACE FUNCTION structure.format_remove_file_path (
      v_string varchar,
      v_prefixlength integer
    )
    RETURNS varchar AS'
    --\\work\\now\\dammit\\test.jpg ,0
    --thisistheprefix\\so\\work\\now\\test.jpg ,15
    DECLARE
    	v_newstring VARCHAR(1000);
    	v_newstring2 VARCHAR(1000);
    	v_newstring3 VARCHAR(1000);
        v_prefixlength_var INTEGER;
        v_position INTEGER;
    
    BEGIN
    
    v_prefixlength_var = COALESCE(v_prefixlength,0);
    v_newstring = COALESCE(v_string,'''');
    
    IF LENGTH(v_newstring)=0 THEN RETURN ''''; END IF;
    
    v_newstring2 = REPLACE(v_newstring,chr(92),''|'');
    
    v_position = strpos(SUBSTRING(v_newstring2,v_prefixlength_var+1,99), ''|'');
    
    IF v_position = 0 THEN 
    	v_newstring3 = SUBSTRING(v_newstring2,v_prefixlength_var+1,99);
    ELSE
    	v_newstring3 = reverse(v_newstring2);
    END IF; 
    
    v_newstring3 = COALESCE(v_newstring3,'''');
    
    RETURN v_newstring3;
    
    END;
    'LANGUAGE 'plpgsql'
    VOLATILE
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 100;
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    This can be done much shorter:
    Code:
    create or replace function last_element(line text, delimiter text) 
      returns text
    as
    $body$
    declare
      elements text[];
    begin
       elements := string_to_array(line, delimiter);
       return elements[array_length(elements,1)];
    end;
    $body$
    language plpgsql;
    Here is a SQLFiddle example: http://sqlfiddle.com/#!12/ed069/1

    Note that backslashes do not need escaping in string literals (at least not with any modern Postgres version, where "standard_conforming_strings" is set to true by default).
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    2
    Rep Power
    0
    that's really clever, thank you. But... I am using "standard_conforming_strings" is set to false for backward compatibility. so your function fails b/c i'm trying to parse "\this\using\backslashes.jpg"

    if it can't be done, i suppose we could go to the effort of finding all the places that will break when i set that to true...

    any ideas on compatibility without conforming strings?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by jonathansfl
    that's really clever, thank you. But... I am using "standard_conforming_strings" is set to false for backward compatibility.
    backward compatibility of what? Postgres itself doesn't need it and it is recommended to set it to true.

    so your function fails b/c i'm trying to parse "\this\using\backslashes.jpg"
    Well, then use \\ instead of \ in the strings and the delimiter.

    Another alternative is to turn on non-standard strings inside the function:
    Code:
    create or replace function last_element(line text, delimiter text) 
      returns text
      SET standard_conforming_strings = true 
    as
    .....
    i suppose we could go to the effort of finding all the places that will break when i set that to true
    That is definitely the better way to go.

    any ideas on compatibility without conforming strings?
    You can always set escape_string_warning to true and see how many warnings you get.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo