I have a sql table named Stores that is standard, has columns
'city','state','storename'

The problem is that some stores have been data entered as
'st. paul'
'saint paul'
'east st. paul'
'west st. paul'
'west saint paul'.

my problem is that when the user queries for 'st. paul' it needs to
match ALL of the st. paul cities no matter how they were data entered.
So what I do is split function the "st. paul" and put it into a table
datatype... well see how I do it below.


ALTER function dbo.mt_getAllNamesForCity
(
@city varchar(100), @state varchar(1000)
)
returns @AllNamesForCity table (city varchar(200))


begin


--********* GET CENTER TARGET LATITUDE AND LONGITUDE
******************
declare @lat float(53)
declare @lon float(53)
declare @isGoodCriteria bit

select @lat = latitude, @lon = longitude from citycode where city =
@city and state = @state
if @lat is null
begin
select @lat = latitude, @lon = longitude from store where city =
@city and state = @state and country = 'us'
if @lat is null
begin
set @isGoodCriteria = 0
end
end

declare @Name varchar(100)

DECLARE splitUpName CURSOR FAST_FORWARD FOR --Declare
the cursor
select ( '%' + Value + '%') as spname from dbo.mt_split(@city, ' ')
--this splits the city name into separate words like %st.% and %paul% -
better than putting it into a temp table

OPEN splitUpName --Open the cursor
FETCH NEXT FROM splitUpName INTO @Name --Fetch next record
WHILE @@FETCH_STATUS = 0

BEGIN

insert into @AllNamesForCity (city)
select distinct city from store
where city like @Name and state = @state and
(
3958.75 * ( Atan(Sqrt(1 - power(((Sin(@Lat/57.2958) *
Sin(latitude/57.2958)) + (Cos(@Lat/57.2958) * Cos(latitude/57.2958) *
Cos((longitude/57.2958) - (@Lon/57.2958)))), 2)) /
((Sin(@Lat/57.2958) * Sin(latitude/57.2958)) + (Cos(@Lat/57.2958) *
Cos(latitude/57.2958) * Cos((longitude/57.2958) - (@Lon/57.2958)))))) +
0.0000000000000000001
< 20) -- make sure that the alternate city names it finds are within
20 miles


FETCH NEXT FROM splitUpName INTO @Name

END

CLOSE splitUpName --Close cursor
DEALLOCATE splitUpName --Deallocate cursor

return
end


*** Sent via Developersdex http://www.developersdex.com ***