SQLite Forum

SELECT STATEMENT FOR OGR2OGR IN GIS
Login

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 [link] [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 [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