postmodern-l (like, ilike, limit)

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

Like, ilike Example

The sql like operator provides a little bit of fuzzy string matching in a search. The following is a simple example using the sql like operator in s-sql.

(query (:select 'id 'name

                :from 'countries

                :where (:like 'name "%New%")))

((103 "New Caledonia") (58 "New Zealand") (108 "Papua New Guinea"))

The sql ilike operator provides the same thing, but on a case insensitive basis. The following is a simple example using the sql ilike operator in s-sql.

(query (:select 'id 'name

                :from 'countries

                :where (:like 'name "%NEW%")))

((103 "New Caledonia") (58 "New Zealand") (108 "Papua New Guinea"))

Limit and offset

Note that :limit has 2 possible parameters, the limit and the offset. Note that the :order-by and :limit forms are wrapped around the :select form. The only difference between the two queries is the offset parameter.

(let ((list-limit 2)

      (offset 0))

  (query

   (:limit

    (:order-by

     (:select 'countries.id 'countries.name

              :from 'countries)

     'name)

    '$1 '$2)

   list-limit offset))

((82 "Afghanistan") (130 "Albania"))

(let ((list-limit 2) (offset 2))

  (query

   (:limit

    (:order-by

     (:select 'countries.id 'countries.name

              :from 'countries)

     'name)

    '$1 '$2)

   list-limit offset))

((140 "Algeria") (34 "All"))