sql SELECT Count(*) works on single words only

Question

Post reply

sql SELECT Count(*) works on single words only

markmied

Grasshopper

Points: 16

October 27, 2017 at 6:06 pm

#333424

0 down vote favorite

The following statement works on single words only.

sql = "SELECT Count(*) as Totalab FROM tablename WHERE city = '" & Server.URLEncode(rs("city")) & "'"

For example:

For the city of Toronto this works fine and returns count of 22 (just example),because Toronto is 1 word and this works just fine.

For the city of New York it returns 0 (where it should return count of 62, for example) because New York is 2 words.

Any help is greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99137 More actions · Answer 1

Where are you running your SQL statement from; what you've supplied is not valid SQL? When you concatenate strings in T-SQL use you the + operator. For example SELECT 'Hello + ' ' + world.'; returns "Hello World." Using the & operator would generate an error.

I don't think the problem is the SQL, but whatever you are using to generate it; and that is having problem with 2 word names.

As a proof of point:

CREATE TABLE #City (CityName varchar(30));
GO
INSERT INTO #City
VALUES ('Toronto'),('New York'),('New York');
GO
--Will return 1
SELECT COUNT(*) AS Entries
FROM #City
WHERE CityName = 'Toronto';
--Will return 2
SELECT COUNT(*) AS Entries
FROM #City
WHERE CityName = 'New York';
GO
DROP TABLE #City;
GO

The T-SQL had no problems handling the 2 part name.

Have a look at what is being passed to your SQL Server from your application. This can easily be done by using Extended Events (which has a node named XE Profiler at the bottom of your object explorer in SSMS 17.3). See what it's running and post back if you can't see the problem (with the SQL being run).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Eirikur Eiriksson SSC Guru Points: 182942 More actions · Answer 2

markmied - Friday, October 27, 2017 6:06 PM
0 down vote favorite The following statement works on single words only.
sql = "SELECT Count(*) as Totalab FROM tablename WHERE city = '" & Server.URLEncode(rs("city")) & "'"
For example:
For the city of Toronto this works fine and returns count of 22 (just example),because Toronto is 1 word and this works just fine.
For the city of New York it returns 0 (where it should return count of 62, for example) because New York is 2 words.
Any help is greatly appreciated.

Why don't you create a reference table for Cities, give each city an identity value and then use the identity value within the count statement, much easier and avoids having to deal with common errors like misspelling.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Thom A - Saturday, October 28, 2017 5:33 AM
Where are you running your SQL statement from; what you've supplied is not value SQL?

Classic ASP, it looks like. Maybe PHP. He's building up a SQL string in whichever language, will execute it afterwards.
Massive SQL Injection vulnerability

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Thom A SSC Guru Points: 99137 More actions · Answer 4

GilaMonster - Saturday, October 28, 2017 9:52 AM
Thom A - Saturday, October 28, 2017 5:33 AM
Where are you running your SQL statement from; what you've supplied is not value SQL?
Classic ASP, it looks like. Maybe PHP. He's building up a SQL string in whichever language, will execute it afterwards.
Massive SQL Injection vulnerability

Don't I know it... When I moved to my current employee one of the "internal" sites was made purely of SQL string being built in the language. I given up showing them what it can do as they won't commit time to fixing it. Even shown them how bad it was in the dev environment.

I'm honestly waiting for the day it gets compromised and I'll happily watch the fireworks while telling them "I told you so."

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

It's been quite a while since I've used classic ASP, but IIRC, doesn't Server.UrlEncode convert spaces to a + sign? That would be the problem, because 'New York' does not equal 'New+York'.

Don't discount what was said by Gail and Thom about SQL injection. It's very real and no matter what company you work for, you don't want to hear about it on the news.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sql SELECT Count(*) works on single words only

Cookies on SQLServerCentral