SQL 2012 Error in LEFT or SUBSTRING function

Question

I am trying to execute the following SQL query:

SELECT TMP.*,COUNT(*) OVER () AS rCount 
FROM (
    SELECT venueID, 
           venueName AS venueName, 
           venueSpanish AS spanish, 
           venueAddress + ', ' + venueCity + ', ' + venueState + ' ' + venueZip AS venueAddress, 
           venueLatLong AS coordinates, 
           CONVERT(VARCHAR, venueEventDate, 101) + ' @ ' + CONVERT(VARCHAR,venueTime) AS dateAndTime, 
           SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1) AS Lat, 
           SUBSTRING(venueLatLong, CHARINDEX(',', venueLatLong) + 1, 1000) AS Lng, 
           (round(3959 * acos (cos(radians('35.0935409')) * 
                cos(radians(SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1))) * 
                cos(radians(SUBSTRING(venueLatLong, CHARINDEX(',', venueLatLong) + 1, 1000)) - 
                radians('-85.0856761')) + 
                sin(radians('35.0935409')) * 
                sin(radians(SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1)))), 1, 1)) AS distance 
    FROM meetUpMarkers) TMP 
WHERE distance < 30

However, I am getting this as an error when doing so:

Msg 537, Level 16, State 2, Line 1

Invalid length parameter passed to the LEFT or SUBSTRING function.

Any help would be great to solve this issue!

You may want to consider using SQL Server Spatial Data Types: http://stackoverflow.com/q/551894/3854195 — Morpheus, Oct 26 '15 at 15:05

score 2 · Answer 1 · answered Oct 26 '15 at 14:57

2

It's probably this line:

SUBSTRING(venueLatLong, 1, CHARINDEX(',', venueLatLong)-1) AS Lat

there's no comma in venueLatLong and this results in -1 for the length

Why don't you store Latitude and Longitude in two numeric columns instead of a VarChar?

answered Oct 26 '15 at 14:57

dnoeth

54,996
3
29
45

Yeah it looks as though *some* records did not have "xx.xxxxxx,-xx.xxxxxxx" for lat/long so therefore it was throwing that error. – StealthRT Oct 26 '15 at 15:14

score 2 · Accepted Answer · answered Oct 26 '15 at 14:57

2

The problem could be with the arguments passed to the substring function. This condition is being repeated in the query.

CHARINDEX(',', venueLatLong)

If the venueLatLong doesn't contain ,, this argument to the substring is invalid.

This can be avoided by including a where clause.

WHERE CHARINDEX(',', venueLatLong) > 0

answered Oct 26 '15 at 14:57

Vamsi Prabhala

46,335
4
30
47

Yeah it looks as though *some* records did not have "xx.xxxxxx,-xx.xxxxxxx" for lat/long so therefore it was throwing that error. – StealthRT Oct 26 '15 at 15:14

SQL 2012 Error in LEFT or SUBSTRING function

2 Answers2