Splitting delimited text in SQL

Split delimited text:

declare @userinput varchar(100) = ‘GREAT ALPINE ROAD – BRIGHT’
declare @roadname varchar(100)
declare @locality varchar(100)

select
@roadname = rtrim(PARSENAME(replace(@userinput, ‘-‘,’.’),2)),
@locality = ltrim(PARSENAME(replace(@userinput, ‘-‘,’.’),1))

declare @geo geometry

select @geo = ogr_geometry
from [GISProduction].[dbo].[locality_polygon]
where locality = @locality

select pfi
from [GISProduction].[dbo].[tr_road] as a
where a.ogr_geometry.STIntersects(@geo) = 1
and a.ezi_rdname = @roadname

Advertisements