Skip to content

Instantly share code, notes, and snippets.

View KeithHenry's full-sized avatar
🏠
Working from home

Keith Henry KeithHenry

🏠
Working from home
View GitHub Profile
create function dbo.udf_OrdnanceSurvey_WGS84 (@east int, @north int)
returns geography
as
begin
if @east is null
or @north is null
or (@east = 0 and @north = 0) -- 0,0 is a legitmate co-ordinate, but in our DB it's used instead of missing locations
return null
-- Adapted from https://stackoverflow.com/a/12317217/905
create function dbo.udf_OSGrid_WGS84 (@east int, @north int)
returns geography
as
begin
-- reference implementation: http://www.movable-type.co.uk/scripts/latlong-os-gridref.html
if @east is null
or @north is null
return null
@KeithHenry
KeithHenry / searchToXml.sql
Last active April 11, 2018 17:37
SQL UDF to create an XML intermediate step to convert the '(A or B) and (C or D)' style complex searches
create function dbo.searchToXml(@input varchar(max)) returns xml
as
begin
set @input = ltrim(rtrim(@input))
-- Mismatched ( and ) means we can't parse with this method
if len(replace(@input, '(', '')) <> len(replace(@input, ')', ''))
return null;
set @input = replace(replace(replace(replace(replace(replace(replace(