Thread: Regular expression on a string problem.
Here is a problem I'm having with a function I've created. It should be returning a varchar value no matter what the input is but I'm getting a null value back. Then it says in the error that I'm using "nonstandard use of escape in a string literal at character 9". What do you think this should be changed to?
Query OK (0.45 sec)
Return Value: NULL
WARNING: nonstandard use of escape in a string literal at character 9
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
QUERY: SELECT '/(\.PR\.)|(\.PR$)/'
CONTEXT: PL/pgSQL function "clean_name" line 17 at assignment
Here is the function. p_text is a varchar that might have "PR" in it.
DECLARE
var_regex_pattern varchar;
BEGIN
-- If "PR" is not in the text variable.
IF position('PR' in p_text) = 0 THEN
var_regex_pattern = '/\./';
RETURN substring(p_text from var_regex_pattern);
END IF;
IF position('PR' in p_text) > 0 THEN
-- If "PR" is in the text variable.
var_regex_pattern = '/(\.PR\.)|(\.PR$)/';
RETURN substring(p_text from var_regex_pattern);
END IF;
END;
Query OK (0.45 sec)
Return Value: NULL
WARNING: nonstandard use of escape in a string literal at character 9
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
QUERY: SELECT '/(\.PR\.)|(\.PR$)/'
CONTEXT: PL/pgSQL function "clean_name" line 17 at assignment
Here is the function. p_text is a varchar that might have "PR" in it.
DECLARE
var_regex_pattern varchar;
BEGIN
-- If "PR" is not in the text variable.
IF position('PR' in p_text) = 0 THEN
var_regex_pattern = '/\./';
RETURN substring(p_text from var_regex_pattern);
END IF;
IF position('PR' in p_text) > 0 THEN
-- If "PR" is in the text variable.
var_regex_pattern = '/(\.PR\.)|(\.PR$)/';
RETURN substring(p_text from var_regex_pattern);
END IF;
END;
Paul Mendoza wrote: > Here is a problem I'm having with a function I've created. It should be > returning a varchar value no matter what the input is but I'm getting a null > value back. Then it says in the error that I'm using "nonstandard use of > escape in a string literal at character 9". What do you think this should be > changed to? > > Query OK (0.45 sec) > Return Value: NULL > WARNING: nonstandard use of escape in a string literal at character 9 > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > QUERY: SELECT '/(\.PR\.)|(\.PR$)/' It's just the new (SQL-standard) string formatting rules. Old-style PG strings allowed C-style backslash escapes (typically for newlines etc, as the hint says). The SQL standard doesn't, so an "escape" string format was introduced to allow the old behaviour along with some config settings (standard_conforming_strings, escape_string_warning, backslash_quote) Now, you're not actually using the backslash as a C-escape, just as a protector to the regexp. So, escape_string_warning is warning you unnecessarily. Now, none of that is why you're getting no results. You're getting no results because you're using Perl-compatible regexps and PostgreSQL is expecting POSIX-style*. See chapter 9.7.3 for details. * - I keep doing this myself -- Richard Huxton Archonet Ltd
Paul Mendoza wrote: > Sent: Saturday, September 08, 2007 12:53 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Regular expression on a string problem. > > Here is a problem I'm having with a function I've created. It > should be returning a varchar value no matter what the input > is but I'm getting a null value back. Then it says in the > error that I'm using "nonstandard use of escape in a string > literal at character 9". What do you think this should be changed to? > > Query OK (0.45 sec) > Return Value: NULL > WARNING: nonstandard use of escape in a string literal at character 9 > HINT: Use the escape string syntax for escapes, e.g., E'\r\n'. > QUERY: SELECT '/(\.PR\.)|(\.PR$)/' > CONTEXT: PL/pgSQL function "clean_name" line 17 at assignment > > > Here is the function. p_text is a varchar that might have "PR" in it. > > DECLARE > var_regex_pattern varchar; > > > BEGIN > > > > -- If "PR" is not in the text variable. > IF position('PR' in p_text) = 0 THEN > var_regex_pattern = '/\./'; > RETURN substring(p_text from var_regex_pattern); > END IF; > > IF position('PR' in p_text) > 0 THEN > -- If "PR" is in the text variable. > var_regex_pattern = '/(\.PR\.)|(\.PR$)/'; > RETURN substring(p_text from var_regex_pattern); > END IF; > > > END; The warning message and the problem that you encounter are not really related. You get the warning because standard_conforming_strings is off and you use a string literal with a backslash in it without preceeding it with the string escape syntax token E. Because standard_conforming_strings is off, the backslash is interpreted a an escape character and consequently when you write '/(\.PR\.)|(\.PR$)/', the string actually becomes '/(.PR.)|(.PR$)/'. You need to have double backslashes to represent single ones and you need to say E'/(\\.PR\\.)|(\\.PR$)/' if you want to avoid the warning. This probably causes the behaviour that surprises you. To help you further, I'd have to know what you actually want to achieve, i.e. which output you desire for which input. The whole function could be written as a single SQL statement using the CASE construct. Yours, Laurenz Albe