Proper case returns results, lower case doesnt

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Proper case returns results, lower case doesnt

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2010-12-31 : 08:54:14

Hey folks,

I have a table full of cities and states with their longitude and latitude to figure out distance. if i type in @Title = 'a' and @Location = 'Bellmore' and @Miles = '30' i get results. however if i use 'bellmore' for the location i get 0 results. any ideas?

USE [Floofie]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [floofie_2010].[Search_Type_TitleLocation]
(
@Location varchar(250) = Null,
@Miles Float,
@Title varchar(250)
)
As
set nocount on

Declare @Latitude Float(10)
Declare @Longitude Float(10)

-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From [floofie_2010].[Location] Where CityState LIKE '%' + @Location + '%'

Select
ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [floofie_2010].[Listings].[CityState], ActiveListing, UserName,

-- Zip_Name,
Round(Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))),2) As Distance
From
[floofie_2010].[Location] inner join [floofie_2010].[Listings] on [floofie_2010].[Location].[CityState] = [floofie_2010].[Listings].[CityState]
Where
Longitude Is Not Null
And Latitude Is Not Null
And @Miles >=
(
3958.75 * ACos(Sin(@Latitude/57.2958) *
Sin(Latitude/57.2958) +
Cos(@Latitude/57.2958) *
Cos(Latitude/57.2958) *
Cos(Longitude/57.2958 - @Longitude/57.2958))
)
AND (ActiveListing = 1) AND (ListingSubject LIKE '%' + @Title + '%') OR (ListingDescription LIKE '%' + @Title + '%')
Group by ListingID, ListingDate, ListingSubject, ListingDescriptionPreview, Salary, [floofie_2010].[Listings].[CityState], ActiveListing, UserName
Order by Distance, ListingDate Desc
return

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-31 : 09:51:53

You are probably using a case sensitive collation.

N 56°04'39.26"
E 12°55'05.63"

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-03 : 01:52:22

To Check the collation; Right Click on the database object using the Object Explorer and select Properties. Check the Collation e.g. SQL_Latin1_General_CP1_CI_AS. Where CI is Case Insensitive and CS is Case Sensitive.

Kristen
Test

22859 Posts

Posted - 2011-01-03 : 04:21:15

If it is case-sensitive collation this may fix it.

Select @Latitude = Latitude, @Longitude = Longitude From [floofie_2010].[Location] Where CityState LIKE '%' + @Location + '%' COLLATE SQL_Latin1_General_CP1_CI_AS

I sure hope you don't have much data in your table, or you don't use this query very often, or it may well become a significant bottleneck (given the way it is written).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources