This is related to the Google Maps web part from an earlier post. I have latitude and longitude columns in a database table. Both are Float datatypes. I needed to build a URL string for the push-pin description box. That would seem straight-forward enough, however, I was receiving an error on converting a float value to a varchar. Below is my first take on the SQL to create my location list. This location list is connected to Sharepoint as an external content type and external BCS list.<\/p>\n
INSERT INTO LocationsPins (Title, Description, Latitude, Longitude)\nSelect Address as Title, ('URL: <a href=\"\/sandbox\/lists\/locationdata\/Read%20List.aspx?Lat='\n+ Latitude\u00a0+ '\">Location Detail<\/a>') AS Description, Latitude, Longitude<\/pre>\nFrom LocationData\nWhere Latitude IS NOT NULL\nand Latitude > 0\nAND Longitude IS NOT NULL\nAND Address IS NOT NULL\nAND City is NOT NULL\nAND State is NOT NULL\nAND Zip is NOT NULL\nGROUP BY Address, Latitude, Longitude<\/pre>\nThat by itself threw the error regarding converting a float to varchar. Using Convert or Cast was not getting what I needed as Latitude can be 6 or more decimal places and those functions would add trailing Zeroes.<\/p>\n
So, I had to create a Function that would take this into account.<\/p>\n
SET ANSI_NULLS ON\nGO\nSET QUOTED_IDENTIFIER ON\nGO\nALTER FUNCTION [dbo].[FloatConvert]\n(@ParmValue Float)\nRETURNS VARCHAR(25)\nAS\nBEGIN\nDECLARE @RetValue VARCHAR(25)\nSET @RetValue = CAST(CAST(@ParmValue AS DECIMAL(15,7)) AS VARCHAR(20))\nSET @RetValue = REVERSE(@RetValue)\nWHILE SUBSTRING(@RetValue,1,1) = 0\nBEGIN\nSET @RetValue = SUBSTRING(@RetValue,2,25)\nEND\nSET @RetValue = REVERSE(@RetValue)\nRETURN @RetValue\nEND<\/pre>\nThe SQL query (description field) to build the list looks like this;<\/p>\n
Select Address as Title, (‘URL: <a href=”\/sandbox\/lists\/locationdata\/Read%20List.aspx?Lat=’+ dbo.FloatConvert(Latitude)\u00a0+ ‘”>Location Detail<\/a>’) AS Description, Latitude, Longitude<\/p>\n
Hope this helps someone as I’ve seen postings regarding this conversion during some Google searches.<\/p>\n","protected":false},"excerpt":{"rendered":"
This is related to the Google Maps web part from an earlier post. I have latitude and longitude columns in a database table. Both are Float datatypes. I needed to build a URL string for the push-pin description box. That would seem straight-forward enough, however, I was receiving an error […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,11],"tags":[35,49,107],"class_list":["post-120","post","type-post","status-publish","format-standard","hentry","category-computer","category-software","tag-convert-float","tag-google-maps","tag-sql-server"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=120"}],"version-history":[{"count":0,"href":"https:\/\/markwheeler.com\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions"}],"wp:attachment":[{"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/markwheeler.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}