T-SQL - Weird Results when I do a SELECT

1
votes

I am trying to search for any string that contains ";" in a column of a table which results in some weird results.

It is not only returning me the results that have ";" but also returns those rows which don't contain any ";" but instead contains a "'".

Here is my query:

select *
from fields
where Comment like '%;%'

and returned results is

Row 1 & 2 instead of 3 & 4 records

Any idea?

I am adding following table structure and some insert statements for reference. Though the result will differ now 

>/****** Object: Table [dbo].[Fields]  Script Date: 08/10/2010 14:18:56 ******/
SET ANSI_NULLS ON
GO

>SET QUOTED_IDENTIFIER ON
GO

>SET ANSI_PADDING ON
GO

>CREATE TABLE [dbo].[Fields](
    [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Comment] [text] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

>SET ANSI_PADDING OFF
GO

>INSERT INTO Fields (Comment) VALUES ('Moody''s Corp')
Go

>INSERT INTO Fields (Comment) VALUES ('0% as core business is ex-growth and hasn''t grown in a decade.')
Go

>INSERT INTO Fields (Comment) VALUES ('bphivgcofc ;')
Go

>INSERT INTO Fields (Comment) VALUES ('Newsflow - recommendations on use of paracetamol. Will probably takeoff EPS; in a worst-case but low probability scenario could be a 3% hit to earnings.')
Go

------- SOLVED -----

I think the problem is within the interface I am using to query the database. This database is externally hosted so the provider has given us an interface to access that data and it seems there is a problem in that interface.

Thanks for everyone's help and suggestions.

4
tsql
Which version of SQL Server are you using & what is datatype of Comment? I have tested it with 'VARCHAR', 'NVARCHAR', 'TEXT', 'CHAR' in SQL Server 2005 and get no result. – Kashif
@upvoter, are you also facing same problem? Please share your thoughts. – Kashif
I am using SQL Server 2005 and the data type is text. it still isn't working for me. – TheITGuy
I have just copy paste your code in SQL Server 2005 and for me result is 3, 4. I am not sure what is wrong but test your code in a new test db. – Kashif

4 Answers

3
votes

Are you getting confused between your fields?

You are searching where the comments equal LIKE %;%, and then you appear to be returning the result of the company name? Perhaps it is a comment, but it looks like it isn't. Perhaps your query should be:

select companyName from fields where companyName like '%;%'

or

select coment from fields where Comment like '%;%'

Also never use asterix, select the fields you want to return. This question is a good example of why you should do that! Make it easier to debug in these circumstances.

0
votes

Sounds strange.
Try

select case when "Moody's Corp, Lowe's Companies, Inc., L'Oreal SA" like '%;%' then 1 end

0
votes

First things first, get rid of the text datatype. This has been deprecated and you need to replace all instances of this as soon as you can. Use nvarchar(max) or varchar(max) instead.

I get the correct results with your select statement and with Chryss's statement where she/he escapes the semicolon.

You say in one of the comments that this is a simplified version of your real query, perhaps you simplified the problem away?

0
votes

I think its the problem within the interface I am using to query the database. This database is externally hosted so provider has given us the interface to access that data and it seems there is a problem in that interface.

Thanks for everyone's help and suggestions.