Hi! Can someone please help me figure out why and how this query works? I can't understand the logic behind it. Thanks so much

Question

Screenshot illustrating the problem

TASK: separate the location column into two columns for latitude and longitude.

Location column:

(37.709725805163, -122.413623946206)

Query:

SELECT 
    location,
    TRIM(leading '(' FROM LEFT(location, POSITION(',' IN location) - 1)) AS latitude,
    TRIM(trailing ')' FROM RIGHT(location, LENGTH(location) - POSITION(',' IN location) ) ) AS longitude
FROM 
    tutorial.sf_crime_incidents_2014_01

PROBLEM: I want to know why this query works because I don't understand it at all.

Answer 1

You have to read it from the outside in. It helps to space it all out.

TRIM(
  leading '(' FROM 
    LEFT(
      location, 
      POSITION(',' IN location) - 1
    )
)

POSITION(',' IN location) returns the offset of the first , in location. In your example (37.709725805163, -122.413623946206) that's 17. Subtracting one excludes the comma, 16.

LEFT(location, POSITION(',' IN location) - 1) evaluates in your example to left(location, 16) and returns everything in location which is left of the given position: first , except the comma. (37.709725805163.

Finally TRIM( leading '(' from LEFT(location, POSITION(',' IN location) - 1)) evaluates to TRIM( leading '(' from (37.709725805163) and removes everything from the string before and including the first '('. 37.709725805163.

You should be able to figure out the second one.