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

Img of Problem

— TASK: Separate the location field into two columns for latitude and longitude

Location Field: (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.

>Solution :

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.