S-SQL Special Characters

S-SQL Examples Home Page

A B C D E F G H I J K L M N O P R S T U V W Special Characters Calling Postgresql Stored Functions and Procedures

You can generally assume that most Postgresql operators will act similarly to the following:

:= The Equal operator

(query (:select 'id 'name :from 'regions :where (:= 'name "South America")))

((7 "South America"))

:+ The Plus operator

(query (:select (:+ 'id 12) 'name
                :from 'regions
                :where (:= 'name "South America")))
((19 "South America"))

:<> The Not Equals or Greater or Lesser than operators

The not equals operator 

(query (:select 'id 'name
                :from 'regions
                :where (:<> 'name "Africa")))

((3 "Central America") (5 "Middle East") (6 "North America") (7 "SouthAmerica") (8 "Central Asia") (9 "Pacific") (10 "Caribbean") (11 "Eastern Europe") (4 "Western Europe")  (2 "Asia"))

:| Concatenating Columns

The concatenation operator combines two or more columns into a single column return. First, consider the query on a raw sql string: 

(query "(SELECT countries.id, (countries.name | '-' | regions.name)
         FROM countries, regions
         WHERE ((regions.id = countries.region_id)
               and (countries.name = 'US')))")

((21 "US-North America"))

Now consider the result using s-sql. 

(query (:select 'countries.id (:| 'countries.name "-" 'regions.name)
                :from 'countries 'regions
                :where (:and (:= 'regions.id 'countries.region-id)
                             (:= 'countries.name "US"))))

((21 "US-North America"))

:~, :!~, :~* Regex Match

Regular expression matching operators. The exclamation mark means 'does not match', the asterisk makes the match case-insensitive. 

(query (:select (:regexp_match "foobarbequebaz" "bar.*que")) :single)

#("barbeque")

(query (:select (:regexp_match "foobarbequebaz" "bar.~que")) :single)

:NULL

(query (:select (:~ "foobarbequebaz" "bar.*que") ) :single)

t

(query (:select (:!~ "foobarbequebaz" "bar.*que") ) :single)

nil

(query (:select (:~ "foobarbequebaz" "barque") ) :single)

nil

(query (:select (:~ "foobarbequebaz" "barbeque") ) :single)

t

(query (:select (:~ "foobarBequebaz" "barbeque") ) :single)

nil

(query (:select (:~* "foobarBequebaz" "barbeque") ) :single)

t

(query (:select 'id 'text :from 'text-search :where (:~ 'text "sushi")))