wrong sintax 1

[filipe29]

what's wrong with this syntax?

select * from cliente
where nome like =1 or like =2 is null

 

[SQLDenis]

I assume 1 and 2 are oracle variables?


declare @p1 varchar (10) @p2 varchar (10)
select @p1 = '%a%', @p2 ='%b%'


select * from cliente
where nome like @p1
or nome like '@p2'
or nome is null

what are you trying to accomplish with this query?


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Remove last "like".

And what the frell is =1 ?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SimonSellick]

SQL Denis - does '@p2' substitute, or do you mean:

declare @p2 varchar(10)
select @p2 = 'b'

select
...
and nome like '%' + :p2 + '%'

?

 

[SQLDenis]

Substitutes

declare @p1 varchar (10) @p2 varchar (10)
select @p1 = '%a%', @p2 ='%b%'


select * from cliente
where nome like @p1
or nome like '@p2'
or nome is null

or

declare @p1 varchar (10) @p2 varchar (10)
select @p1 = 'a', @p2 ='b'


select * from cliente
where nome like '%' + @p1 + '%'
or nome like '%' + @p2' + '%'
or nome is null

I just find it easier to do it at the variable level

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SimonSellick]

That's interesting. If you want to use 'like' to search for an @, do you have to escape it somehow?

 

[SQLDenis]

Ok I misunderstood

when I do this @p2 ='%b%'
I am basically creating a shortcut for
like '%' + @p2 + '%'
or in plain text like '%b%'


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[filipe29]

sorry i forgot to mention that p1 is a parameter

 

[filipe29]

and p2 also

 

[SimonSellick]

Yes, I see that; but how does

select * from cliente
where nome like @p1 
or nome like '@p2'
or nome is null

'know' that you mean the content of the variable p2, not the string '@p2'?

 

[SQLDenis]

Ok now I see
I didn't see the tick sround the @p2

should be

select * from cliente
where nome like @p1
or nome like @p2
or nome is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

filipe29,

here is an example against that you can run in pubs

use pubs

declare @p1 varchar (10) ,@p2 varchar (10)
select @p1 = '%a%', @p2 ='%b%'

select au_lname,au_fname,phone from authors
where au_lname like @p1
or au_lname like @p2
or au_lname is null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SimonSellick]

Oh, rats - I thought that MS must have enhanced SQL Server to be more like PHP. And I was going to give you a star. Still, you might be interested in a virtual display cabinet that I have for sale, to hold all your other ones.

 

[SQLDenis]

SimonSellick,

star for you for making me laugh on such a miserable cold day (Princeton, New Jersey)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Congratulations, Denis for being voted tip master of the week.

That alone should cheer you up on a miserably cold day.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

Thanks George

And to all the people who gave me stars



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[filipe29]

Hi again.i think that in adoquery the null function doesnt seems to work.(sql server 2000)

Now i have :

select "bla bla bla"
where nome like aram1 or aram1 is null

and nothing happens when param1 is null