Squeal.PostgreSQL.Expression.Text

text functions and operators

Text Function

lower :: null 'PGtext --> null 'PGtext

>>> printSQL $ lower "ARRRGGG"
lower((E'ARRRGGG' :: text))

upper :: null 'PGtext --> null 'PGtext

>>> printSQL $ upper "eeee"
upper((E'eeee' :: text))

charLength :: null 'PGtext --> null 'PGint4

>>> printSQL $ charLength "four"
char_length((E'four' :: text))

like :: Operator (null 'PGtext) (null 'PGtext) ('Null 'PGbool)

The like expression returns true if the string matches the supplied pattern. If pattern does not contain percent signs or underscores, then the pattern only represents the string itself; in that case like acts like the equals operator. An underscore (_) in pattern stands for (matches) any single character; a percent sign (%) matches any sequence of zero or more characters.

>>> printSQL $ "abc" `like` "a%"
((E'abc' :: text) LIKE (E'a%' :: text))

ilike :: Operator (null 'PGtext) (null 'PGtext) ('Null 'PGbool)

The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale.

>>> printSQL $ "abc" `ilike` "a%"
((E'abc' :: text) ILIKE (E'a%' :: text))

replace :: '[null 'PGtext, null 'PGtext, null 'PGtext] ---> null 'PGtext

Over the string in the first argument, replace all occurrences of the second argument with the third and return the modified string.

>>> printSQL $ replace ("string" :* "from" *: "to")
replace((E'string' :: text), (E'from' :: text), (E'to' :: text))

strpos :: '[null 'PGtext, null 'PGtext] ---> null 'PGint4

Determines the location of the substring match using the strpos function. Returns the 1-based index of the first match, if no match exists the function returns (0).

>>> printSQL $ strpos ("string" *: "substring")
strpos((E'string' :: text), (E'substring' :: text))