Product Documentation

Appearance

POSITION ​

Return the starting position of a character string within another string. See also STRPOS.

POSITION(substring IN string)
premdb=# SELECT name, POSITION('City' IN name) pos 
FROM team WHERE POSITION('City' IN name)>0;
     name       | pos 
-----------------+-----
 Birmingham City |  12
 Bradford City   |  10
 Cardiff City    |   9
 Coventry City   |  10
 Hull City       |   6
 Leicester City  |  11
 Manchester City |  12
 Norwich City    |   9
 Stoke City      |   7
 Swansea City    |   9
(10 rows)

For example, which team names start with their respective city names? The position column returns 1 for several team names because the city name starts at position 1 in the team name.

premdb=# SELECT name, city, POSITION(city IN name) FROM team 
WHERE city IS NOT NULL ORDER BY city;
         name           |     city      | position 
-------------------------+---------------+----------
 Barnsley                | Barnsley      |        1
 Aston Villa             | Birmingham    |        0
 Birmingham City         | Birmingham    |        1
 West Bromwich Albion    | Birmingham    |        0
 Blackburn Rovers        | Blackburn     |        1
 Blackpool               | Blackpool     |        1
 Bolton Wanderers        | Bolton        |        1
 Bournemouth             | Bournemouth   |        1
 Bradford City           | Bradford      |        1
 Burnley                 | Burnley       |        1
 Cardiff City            | Cardiff       |        1
 Coventry City           | Coventry      |        1
 Derby County            | Derby         |        1
 Hull City               | Hull          |        1
 Ipswich Town            | Ipswich       |        1
 Leeds United            | Leeds         |        1
 Leicester City          | Leicester     |        1
 Everton                 | Liverpool     |        0
 Liverpool               | Liverpool     |        1
 Arsenal                 | London        |        0
 Charlton Athletic       | London        |        0
 Chelsea                 | London        |        0
...

Parent topic:String Functions