### Thread: Postgresql reverse string with backslash?

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. No Profile Picture
Contributing User
Devshed Frequenter (2500 - 2999 posts)

Join Date
Oct 2003
Location
Germany
Posts
2,784
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).
3. 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?
4. No Profile Picture
Contributing User
Devshed Frequenter (2500 - 2999 posts)

Join Date
Oct 2003
Location
Germany
Posts
2,784
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.