CSV: Reverse order, comma count queries #places #AI

#1 · 2025-08-14, 2:01 pm

Quote from Tom Holden on 2025-08-14, 2:01 pm
Two SQLite queries developed by DeepSeek under my direction:
Update the Reverse column in PlaceTable from the Name, a comma-separated string
Count the number of commas in Place Name for each Place
These queries can be adapted to other SQLite database columns that employ comma-separated strings.
Problem
These queries were created when I was asked to convert Place Details into Places in a very large database with 48,000 records in the PlaceTable. I was able to use my old scripts from Places – Conversion of Place Details to Places to do the conversion but RM9 and RM10 have an added column in PlaceTable since those scripts were developed. The Reverse column is another un-normalised element that RM Inc has employed supposedly to expedite display of Place Names in reverse order in response to the "Reverse places" control on the Places pane on the More Options list under the 3-dot vertical ellipsis. That field is updated when a place is added or edited by the RM application but is untouched by my old script which resulted in an empty or null Reverse for the new Places it created. That made the reversed places display virtually useless. I needed a way to update Reverse from the place Name.
Analysis
The Place Names resulting from the conversion are stored in the PlaceTable in a comma-separated string of elements typically containing placedetail (itself could have commas), municipality, county, state/prov, country. However, users are unconstrained by the software and any number and nature of elements might be present, including phrases and complete sentences. There could be any number of commas in a Name string, it could be an empty string or a null or have consecutive commas. What we need in the Reverse column is the same elements in reverse order, e.g., country, state/prov, county, municipality, placedetail (itself could have commas).
Solution
From the RM6 or RM7 era, I had developed PlaceReverse.sql to display Places with up to 3 commas in reverse order. Looking at it now, it seems like a clumsy method and would not scale well. So I returned to AI, giving DeepSeek another shot at developing a query. While it seemed ponderously slow compared to Gemini Flash 2.5 as it typed out its 'thinking' at human typing speed, the first iteration worked! With a couple more tweaks, we improved its performance to process the 48,000 records from a few minutes to a few seconds and then constrained it to operate only on user-defined Places, ignoring the built-in LDS Temples and any Place Detail records, to achieve the desired results in 2-3 seconds (your mileage will vary).
RM10-PlaceReverse.sqlDownload
Count Commas
While working on this big database, I was encountering occasional anomalies in the resulting combinations of PlaceDetail and Place into Place (I've since concluded, more or less, that it was my human factor that messed a few things up). But with 48,000 Places, a quarter-million people, RM10 was proving exceedingly slow at scrolling the Place List (and just about everything else). I thought a query that counted commas in the Place Name might catch anomalies of the kind I had seen, e.g., two different Place Names concatenated into one. So I asked DeepSeek to help and, kicking myself that I didn't think of this method, it produced:
--count commas in Place Name
SELECT
PlaceID,
CASE
WHEN Name IS NULL THEN NULL
ELSE LENGTH(Name) - LENGTH(REPLACE(Name, ',', ''))
END AS comma_count,
Name
FROM PlaceTable
ORDER BY comma_count DESC
;

Two SQLite queries developed by DeepSeek under my direction:

Update the Reverse column in PlaceTable from the Name, a comma-separated string

Count the number of commas in Place Name for each Place

These queries can be adapted to other SQLite database columns that employ comma-separated strings.

Problem

These queries were created when I was asked to convert Place Details into Places in a very large database with 48,000 records in the PlaceTable. I was able to use my old scripts from Places – Conversion of Place Details to Places to do the conversion but RM9 and RM10 have an added column in PlaceTable since those scripts were developed. The Reverse column is another un-normalised element that RM Inc has employed supposedly to expedite display of Place Names in reverse order in response to the "Reverse places" control on the Places pane on the More Options list under the 3-dot vertical ellipsis. That field is updated when a place is added or edited by the RM application but is untouched by my old script which resulted in an empty or null Reverse for the new Places it created. That made the reversed places display virtually useless. I needed a way to update Reverse from the place Name.

Analysis

The Place Names resulting from the conversion are stored in the PlaceTable in a comma-separated string of elements typically containing placedetail (itself could have commas), municipality, county, state/prov, country. However, users are unconstrained by the software and any number and nature of elements might be present, including phrases and complete sentences. There could be any number of commas in a Name string, it could be an empty string or a null or have consecutive commas. What we need in the Reverse column is the same elements in reverse order, e.g., country, state/prov, county, municipality, placedetail (itself could have commas).

Solution

From the RM6 or RM7 era, I had developed PlaceReverse.sql to display Places with up to 3 commas in reverse order. Looking at it now, it seems like a clumsy method and would not scale well. So I returned to AI, giving DeepSeek another shot at developing a query. While it seemed ponderously slow compared to Gemini Flash 2.5 as it typed out its 'thinking' at human typing speed, the first iteration worked! With a couple more tweaks, we improved its performance to process the 48,000 records from a few minutes to a few seconds and then constrained it to operate only on user-defined Places, ignoring the built-in LDS Temples and any Place Detail records, to achieve the desired results in 2-3 seconds (your mileage will vary).

RM10-PlaceReverse.sqlDownload

Count Commas

While working on this big database, I was encountering occasional anomalies in the resulting combinations of PlaceDetail and Place into Place (I've since concluded, more or less, that it was my human factor that messed a few things up). But with 48,000 Places, a quarter-million people, RM10 was proving exceedingly slow at scrolling the Place List (and just about everything else). I thought a query that counted commas in the Place Name might catch anomalies of the kind I had seen, e.g., two different Place Names concatenated into one. So I asked DeepSeek to help and, kicking myself that I didn't think of this method, it produced:

--count commas in Place Name
SELECT
PlaceID,
CASE
WHEN Name IS NULL THEN NULL
ELSE LENGTH(Name) - LENGTH(REPLACE(Name, ',', ''))
END AS comma_count,
Name
FROM PlaceTable
ORDER BY comma_count DESC
;