SELECT STATEMENT FOR OGR2OGR IN GIS
(1) By anonymous on 2021-10-21 22:06:39 [link] [source]
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 [source]
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] [source]
>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 [link] [source]
Do not concatenate with + but use double pipe