SQL query does not return results unless you use LIKE in the WHERE clause

Question

Post reply

SQL query does not return results unless you use LIKE in the WHERE clause

perry.pierce

SSC Enthusiast

Points: 152
More actions
September 23, 2008 at 11:17 am

#382787

I have a table with a nvarchar field named Violation number that contains data in the following format 5.1.3(38), or 5.1.3.(39) etc etc.
When I query this table with a simple statement like this I get no records returned
SELECT EventNumber, ViolationNumber
FROM tblCharges
WHERE (ViolationNumber = '5.1.3.(38)')
However if I query the data with this statement I get 344 records returned.
SELECT EventNumber, ViolationNumber
FROM tblCharges
WHERE (ViolationNumber LIKE '%38%')
I am new to T SQL and am sure there is an explanation but I can not seem to find it.
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

bcronce SSCrazy Points: 2597 More actions · Answer 1

bcronce

SSCrazy

Points: 2597

September 23, 2008 at 12:09 pm

#875940

ma'b white characters in there?

Gabriel P SSCarpal Tunnel Points: 4289 More actions · Answer 2

perry.pierce (9/23/2008)
I have a table with a nvarchar field named Violation number that contains data in the following format 5.1.3(38), or 5.1.3.(39) etc etc.
When I query this table with a simple statement like this I get no records returned
SELECT EventNumber, ViolationNumber
FROM tblCharges
WHERE (ViolationNumber = '5.1.3.(38)')
However if I query the data with this statement I get 344 records returned.
SELECT EventNumber, ViolationNumber
FROM tblCharges
WHERE (ViolationNumber LIKE '%38%')
I am new to T SQL and am sure there is an explanation but I can not seem to find it.
Thanks

Some things to check:

1) Make sure there are no whitespaces before or after the data. You can modify your first query to check for this by replacing it with:

SELECT EventNumber, ViolationNumber

FROM tblCharges

WHERE (LTrim(RTrim(ViolationNumber)) = '5.1.3.(38)')

2) Check the collation of the database and the column

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

What about like this? The N prior to the value forces it's use as a Nvarchar string.

SELECT EventNumber, ViolationNumber

FROM tblCharges

WHERE (ViolationNumber = N'5.1.3.(38)')

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

perry.pierce SSC Enthusiast Points: 152 More actions · Answer 4

Thanks for the quick response I removed the white space and it is working now.

Trying to figure out a date conversion error now. I'll fight with it for a few days and see if I can figure it out if not I'll be back.

Thanks again.

Jerry Hung SSChampion Points: 12968 More actions · Answer 5

Oops, I was just about to post the test code that couldn't reproduce the issue

DECLARE @test-2 TABLE

(

Violation NVARCHAR(MAX) NOT NULL

)

INSERT INTO @test-2

VALUES (N'5.1.3.(38)'),

(N'5.1.3.(39)'),

(N'5.1.3.(40)')

SELECT *

FROM @test-2

WHERE Violation = '5.1.3.(38)'

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Ron McCullough SSC Guru Points: 63877 More actions · Answer 6

Perry.Pierce for a fuller explanation of the LIKE comparison read

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/581fb289-29f9-412b-869c-18d33a9e93d5.htm

It shows in detail other possible matching patterns that make LIKE a potent search tool in a SELECT statement.

Now a question for you. Who controls the input of data? As suggested by qPriester (use of trim functions) It may be prudent to perform a LTRIM and a RTRIM before inserting the data into a DB table so as to eliminate problems that you have just uncovered, but of course that is a different subject. Using the infamous answer to a T-SQL question "It depends", think about what the database engine has to do with a LIKE statement, i.e., how many records have to be accessed to return all the relevant data. In small tables it probably does not matter excessively, but searching a million row table it can markedly increase the time for the SELECT to complete. Just a thought

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

perry.pierce SSC Enthusiast Points: 152 More actions · Answer 7

Thanks for the suggestions and helpful links. I am just learning SQL so I must admit some of this is beyond my level of expertise, but I am willing to learn and appreciate all the assistance.