Tweak for Sqlite statment
the Statement works as a I needed it to -- except for one small part
(The state field) -- Sqlite does not have same string functions as other SQL.
I need to extract the 2nd position (between commas either from name or reverse)
example the country parts works fine
<Field><Name>Country</Name><Value>' || substr(pt.Reverse, 0, instr(pt.Reverse,', ')+0) ||'</Value></Field>
statement below:
SELECT
null as a1,
'[' || FT.Name || '] ' || pt.Reverse || ' ' || (substr(E.Date,4,4)) as b2, -- name
'[' || FT.Name || '] ' ||
(substr(E.Date,4,4)) || ' ' ||
'[' || ML.LinkID || '] ' ||
'[' || ML.MediaID || '] ' ||
'[' || nt.OwnerID || '] ' ||
'[' || pt.PlaceID || '] ' ||
'[' || E.EventID || '] ' as c3, -- RefNumber
'[' || FT.Name || '] ' || (substr(E.Date,4,4)) || ' ' || pt.Reverse as d4, -- actual text
'Image' as e5, -- comments
null as f6, -- IsPrivate
10003 as g7, -- TemplateID
'<Root><Fields>
<Field><Name>Date</Name><Value>' || substr(E.Date,4,4) ||'</Value></Field>
<Field><Name>Country</Name><Value>' || substr(pt.Reverse, 0, instr(pt.Reverse,', ')+0) ||'</Value></Field>
<Field><Name>State</Name><Value>' ||
'Needs Fixing'
||'</Value></Field>
<Field><Name>City</Name><Value>' || substr(pt.Name, 1, instr(pt.Name,', ')-1) ||'</Value></Field>
<Field><Name>Place</Name><Value>' || pt.Reverse ||'</Value></Field>
</Fields></Root>' as h8, -- fields
julianday('now') - 2415018.5 as i9 -- UTCmodDate
FROM FactTypeTable AS FT
JOIN EventTable AS E ON E.EventType = 18 AND E.OwnerTYPE = 0 AND E.EventType = FT.FactTypeID
LEFT JOIN MediaLinkTable AS ML ON ML.OwnerType = 2 AND ML.OwnerID = E.EventID
LEFT JOIN NameTable as nt ON (nt.OwnerID = E.OwnerID)
LEFT JOIN PlaceTable as pt ON (e.placeID = pt.PlaceID)
WHERE ML.LinkID IS NOT NULL and Details is NOT NULL and nt.IsPrimary and FT.Name is NOT NULL
AND E.OwnerID = 5165
GROUP BY'[' || FT.Name || '] ' || ' ' || (substr(E.Date,4,4)) || ' ' || pt.Reverse
ORDER BY pt.Reverse, (substr(E.Date,4,4))
ps. the part I need to to update is in fields (Part below) to extract State between comma delimiters
<Field><Name>State</Name><Value>' ||
'fix here'
||'</Value></Field>
This is a pretty large tweak but have a look at Place Names - parse and recombine from 10 years ago. Maybe you could make it into a View and get the values you want for City, State, Country from the View.
Since then, recursive CTE was added to SELECT and that might be another approach but maybe more difficult to develop.
@kevync, here's a cobbled together demo using the old parse scripts, modified to operate on the PlaceTable.Name field rather than the Normalized field. It could also be changed to parse the Reverse field. I left them to create temp tables rather than Views - on a large database it would be much slower to cascade Views.
Uploaded files:Here's a general parsing solution using a recursive CTE that I adapted to PlaceTable.Name from a discussion in StackOverflow.
Sample output:
| PlaceID | seq | word | str |
|---|---|---|---|
| 1228 | 0 | , | Millbrook, Mecosta, Michigan, United States, |
| 1228 | 1 | Millbrook | Mecosta, Michigan, United States, |
| 1228 | 2 | Mecosta | Michigan, United States, |
| 1228 | 3 | Michigan | United States, |
| 1228 | 4 | United States | |
| 1229 | 0 | , | Erie, Erie, Pennsylvania, USA, |
| 1229 | 1 | Erie | Erie, Pennsylvania, USA, |
| 1229 | 2 | Erie | Pennsylvania, USA, |
| 1229 | 3 | Pennsylvania | USA, |
| 1229 | 4 | USA |
You would extract the State from seq=3 records, assuming your place names all follow the City, County, State, Country format. Of course, you could operate on the Reverse field in which case seq=2 would be the state.
WITH RECURSIVE split(PlaceID, seq, word, str) AS (
SELECT PlaceID, 0, ',', Name||',' FROM PlaceTable
UNION ALL SELECT
PlaceID,
seq+1,
TRIM(substr(str, 0, instr(str, ','))),
TRIM(substr(str, instr(str, ',')+1))
FROM split WHERE str != ''
) SELECT * FROM split ORDER BY PlaceID, split.seq ASC
;
Read on down through that discussion to the bottom where it links to the sqlean extensions which includes the function text_split(str, sep, n). If you can add that extension to your SQLite manager, it really simplifies your code.
thank you very much for taking time to put this together for me. I will read through it later tonight. A bit surprise that Sqlite does not have other text function like other SQL would make it a little easier. Not that familiar with recursive techniques in use and have only heard how they offer advantages.
Again I appreciate your help.
Kevin
Quote from kevync on 2023-09-19, 6:34 pmA bit surprise that Sqlite does not have other text function like other SQL would make it a little easier.
I thought I had followed up with this link to the sqlean extension for the function
text_split(str, sep, n)
thanks - -maybe I will play with this weekend.