Problems with ESCAPE '@' . . .

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)

Problems with ESCAPE '@' . . .

Author

Topic

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 05:05:29

Hi there!
(first post this is - be gentle ;)

I have a query like this:


SELECT * FROM Names WHERE Name LIKE "%@_T%" ESCAPE "@"

this works perfectly well.
but this query:


SELECT * FROM Names, Jobs WHERE Names.ID = Jobs.nID AND Names.Name LIKE "%@_T%" ESCAPE "@"

gives me an error near 'ESCAPE'.

why is that?

thanks+regards
/christian

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-19 : 06:07:05

Are you using double quotes around your string literals? Try using single quotes, and use the ANSI join syntax:

SELECT * FROM Names
INNER JOIN Jobs
ON Names.ID = Jobs.nID
WHERE Names.Name LIKE '%@_T%' ESCAPE '@'

Owais

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 06:14:06

thanks for such a quick answer.
I use doublequotes but have tried singlequotes as well and there was no difference. but I will just try the mentioned join syntax and tell you about the result.

regards
/christian

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 06:25:27

hi again!

I tried this:


SELECT * FROM RunTime.dbo.DiscreteTag
	INNER JOIN RunTime.dbo.DiscreteLive
	ON RunTime.dbo.DiscreteTag.TagName = RunTime.dbo.DiscreteLive.TagName
	WHERE RunTime.dbo.DiscreteTag.TagName LIKE '%@_STM%' ESCAPE '@'

which throws an syntax error near 'JOIN' . . . ???

regards
/christian

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 06:42:52

Hi there,


SELECT * 
FROM RunTime.dbo.DiscreteTag AS DT
INNER JOIN RunTime.dbo.DiscreteLive AD DL
ON DL.TagName = DT.TagName
WHERE DT.TagName LIKE '%@_STM%' ESCAPE '@'

also, what have you set your QUOTED_IDENTIFIER to?
Is it ON or OFF?

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

caefer
Starting Member

10 Posts

Posted - 2003-08-19 : 06:57:50

Hi Amethystium!

I will try you query in a minute but its not a big difference is it?
just added two aliases?

and (excuse my humble knowledge) what is a QUOTED_IDENTIFIER?
if it is an option to be set on the server, I have to tell you that I can fully access that server but am not allowed to change anything ... ;(

regards
/christian

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-08-19 : 08:19:30

Try having this line at the top of your code.

SET QUOTED_IDENTIFIER ON

------------------------------------------------------------------------------
I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-08-19 : 09:35:42

I dont get it...this query should run fine:


SELECT * FROM RunTime.dbo.DiscreteTag
	INNER JOIN RunTime.dbo.DiscreteLive
	ON RunTime.dbo.DiscreteTag.TagName = RunTime.dbo.DiscreteLive.TagName
	WHERE RunTime.dbo.DiscreteTag.TagName LIKE '%@_STM%' ESCAPE '@'

Can you remove the WHERE part and try to see if you can join these tables first?

Owais

caefer
Starting Member

10 Posts

Posted - 2003-08-20 : 01:20:42

Hi!

I somehow forgot to mention that on top of my sql-server there is IndustrialSQL which may be responsable for this misbehaviour of the queries, although I don't think so..

regards
/christian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources