querybuilder icon indicating copy to clipboard operation
querybuilder copied to clipboard

Postgres over-escaping ?

Open rolivares opened this issue 3 months ago • 0 comments

In order to get this sentence using WhereRaw:

SELECT * 
FROM "condition_tests" 
WHERE (
  "field_varchar_nullable" IS NOT NULL 
  AND unaccent(TRIM(regexp_replace(field_varchar_nullable, E'[\\n\\r]+', '', 'g' )))  ilike '%' || unaccent('le dijo')
)

I've used this string as raw sentence:

var sentence = @"unaccent(TRIM(regexp_replace(field_varchar_nullable, E'[\\n\\r]+', '', 'g' )))  ilike '%' || unaccent('le dijo')";
var query = new Query("condition_tests").WhereNotNull("field_varchar_nullable").WhereRaw(sentence);

I'm getting this expression (note the replace chars near E '"\n\r"+', '', 'g') )

SELECT
  *
FROM
  "condition_tests"
WHERE
  "field_varchar_nullable" IS NOT NULL
  AND unaccent(
    TRIM(
      regexp_replace(field_varchar_nullable, E '"\\n\\r"+', '', 'g')
    )
  ) ilike '%' || unaccent('le dijo')

If you compare results using MSSql and postgresql factory are different on chars "[" and "]".

How can I escape or acomplish the statement: E'[\n\r]+'

Thanks

UPDATE: note the space after "E" on expression => E '"\n\r"+', '', 'g') this is addded qhen symbol "[" is replaced When I've try to escape the symbols with "" that space continues appearing in consequence SQL does not work

rolivares avatar Apr 05 '24 22:04 rolivares