SELECT STATEMENT FOR OGR2OGR IN GIS
(1) By anonymous on 2021-10-21 22:06:39 [link]
I seek the syntax to create APN numbers that are text strings with and without dashes eg XXX-XXX-XXX & XXXXXXXXX with a CASE statement? CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN LEFT(APN_D, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D THe error "near "(": syntax error" any assistance is MUCH appreciated.
(2.1) By Keith Medcalf (kmedcalf) on 2021-10-22 00:14:00 edited from 2.0 in reply to 1 [link]
Neither `LEFT(` or `RIGHT(` are valid syntax. `LEFT(APN_D, 3)` could be `SUBSTRING(APN_D, 1, 3)` `RIGHT(APN_D, 3)` could be `SUBSTRING(APN_D, 7, 3)` (or perhaps `SUBSTRING(APN_D, -3)`) Alternatively, create the SQL functions `LEFT` and `RIGHT`.
(3.1) By Keith Medcalf (kmedcalf) on 2021-10-22 00:08:25 edited from 3.0 in reply to 1 [link]
``` >sqlite SQLite version 3.37.0 2021-10-20 17:10:36 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table apn(apn_d text not null); sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN LEFT(APN_D, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn; Error: near "(": syntax error sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + RIGHT(APN_D, 3) ELSE APN_D end from apn; Error: near "(": syntax error sqlite> select CASE WHEN APN_D NOT LIKE '%-%' AND APN_D NOT LIKE '' THEN SUBSTRING(APN_D, 1, 3) + '-' + SUBSTRING(APN_D, 3, 3) + '-' + SUBSTRING(APN_D, 6, 3) + '-' + SUBSTRING(APN_D,7,3) ELSE APN_D end from apn; sqlite> ```
(4) By anonymous on 2021-10-22 11:36:09 in reply to 3.1
Do not concatenate with + but use double pipe