with with query - dont bring back weird characters

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 2000 Forums

Transact-SQL (2000)

with with query - dont bring back weird characters

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-12-09 : 20:07:56

Hi,

I'm running this query to bring back a list of cities. The problem is I am getting cities with characters like this showing up which is causing my code to break.

Montréal

Isn't this line enough to limit it from coming back?

where patindex('%[^a-zA-Z0-9 ]%', city )

If not how can I make sure no other weird characters are slipping by.

Thanks once again for any help

mike123

CREATE PROCEDURE dbo.select_Cities_Distinct_MostPopular_byStateProv
(
@stateProv varchar(50)
)
AS SET NOCOUNT ON

select top 85 ltrim(city) as city, UD.stateProvID, count(*) as totalMembers, SP.stateProv from tblUserdetails UD

JOIN tblStateProv SP on UD.stateProvID = SP.stateProvID

where patindex('%[^a-zA-Z0-9 ]%', city ) = 0 and city <> '' and SP.stateProv = @stateProv

group by city, UD.stateProvID, SP.stateProv

HAVING COUNT (*) > 2

order by totalMembers desc, city asc

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 20:31:08

That should do it - but shouldn't you include these cities?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-12-09 : 20:44:08

Your right, it would probably be best if i could but theres another problem and that is that these characters are breaking the URL rewriting code which is above what I would like to get into.

It's user inputted data so in my results I have both Montréal
and Montreal, so losing one is actually ok in a way.

I'm really not sure why that clause on there is not getting rid of these results. Any ideas ?

Thanks again

mike123

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-09 : 21:34:49

It's the collation you are using. try
where patindex('%[^a-zA-Z0-9 ]%', city collate latin1_general_bin) = 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2004-12-10 : 01:16:08

awesome.. thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-10 : 11:15:12

SELECT REPLACE('SQL','sql','Do not display me!' collate latin1_general_bin)

oops wrong thread.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources