LIKE comparisons

LIKE comparisons

Post by Norri » Wed, 22 Mar 2000 04:00:00



I found that for the same SQL like statement, different databases return
different results.  I don't know which vendor is correct or more ANSI standard.
For example,

create table a (a char(2), b int)
insert into a values ('a',11)
select * from a where a like 'a'
select * from a where b like 11

MS-SQL
======
a    b          
---- -----------
a    11
(1 row(s) affected)
a    b          
---- -----------
a    11
(1 row(s) affected)

ORACLE
======
A  B        
-- ----------
0 rows selected.
A  B        
-- ----------
a          11
1 row selected.

SYBASE
======
a  b          
-- -----------
a  11          
(1 row(s) affected)

Msg 11021, Level 16, State 2
Function LIKE invoked with wrong number or type of argument(s).

--
http://www.cooper.com.hk
http://sybooks.sybase.com/onlinebooks
http://www.sybase.com.hk

 
 
 

LIKE comparisons

Post by Sybrand Bakke » Wed, 22 Mar 2000 04:00:00


Most likely the first oracle query suffers from a scoping problem: bad
column and table naming.
I would recommend NOT using column names identical to the table name before
you start posting about incorrect results.
Regards,

Sybrand Bakker, Oracle DBA


Quote:> I found that for the same SQL like statement, different databases return
> different results.  I don't know which vendor is correct or more ANSI
standard.
> For example,

> create table a (a char(2), b int)
> insert into a values ('a',11)
> select * from a where a like 'a'
> select * from a where b like 11

> MS-SQL
> ======
> a    b
> ---- -----------
> a    11
> (1 row(s) affected)
> a    b
> ---- -----------
> a    11
> (1 row(s) affected)

> ORACLE
> ======
> A  B
> -- ----------
> 0 rows selected.
> A  B
> -- ----------
> a          11
> 1 row selected.

> SYBASE
> ======
> a  b
> -- -----------
> a  11
> (1 row(s) affected)

> Msg 11021, Level 16, State 2
> Function LIKE invoked with wrong number or type of argument(s).

> --
> http://www.cooper.com.hk
> http://sybooks.sybase.com/onlinebooks
> http://www.sybase.com.hk


 
 
 

LIKE comparisons

Post by Norri » Wed, 22 Mar 2000 04:00:00


Same result after changing the column name:

create table a (c char(2), b int)
;
insert into a values ('a',11)
;
select * from a where c like 'a'
;

C  B        
-- ----------
0 rows selected.


> Most likely the first oracle query suffers from a scoping problem: bad
> column and table naming.
> I would recommend NOT using column names identical to the table name before
> you start posting about incorrect results.
> Regards,
> Sybrand Bakker, Oracle DBA


>> I found that for the same SQL like statement, different databases return
>> different results.  I don't know which vendor is correct or more ANSI
> standard.
>> For example,

>> create table a (a char(2), b int)
>> insert into a values ('a',11)
>> select * from a where a like 'a'
>> select * from a where b like 11

>> MS-SQL
>> ======
>> a    b
>> ---- -----------
>> a    11
>> (1 row(s) affected)
>> a    b
>> ---- -----------
>> a    11
>> (1 row(s) affected)

>> ORACLE
>> ======
>> A  B
>> -- ----------
>> 0 rows selected.
>> A  B
>> -- ----------
>> a          11
>> 1 row selected.

>> SYBASE
>> ======
>> a  b
>> -- -----------
>> a  11
>> (1 row(s) affected)

>> Msg 11021, Level 16, State 2
>> Function LIKE invoked with wrong number or type of argument(s).

>> --
>> http://www.cooper.com.hk
>> http://sybooks.sybase.com/onlinebooks
>> http://www.sybase.com.hk

--
http://www.cooper.com.hk
http://sybooks.sybase.com/onlinebooks
http://www.sybase.com.hk
 
 
 

LIKE comparisons

Post by Mark Townsen » Wed, 22 Mar 2000 04:00:00


Comments inline


> I found that for the same SQL like statement, different databases return
> different results.  I don't know which vendor is correct or more ANSI standard.
> For example,

> create table a (a char(2), b int)
> insert into a values ('a',11)
> select * from a where a like 'a'
> select * from a where b like 11

> MS-SQL
> ======
> a    b
> ---- -----------
> a    11
> (1 row(s) affected)
> a    b
> ---- -----------
> a    11
> (1 row(s) affected)

> ORACLE
> ======
> A  B
> -- ----------
> 0 rows selected.
> A  B
> -- ----------
> a          11
> 1 row selected.

These aren't the results I get. See below

Connected to:
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production

SQL> create table a (a char(2), b int);

Table created.

SQL> insert into a values ('a',11);

1 row created.

SQL> select * from a where a like 'a';

A           B
-- ----------
a          11

SQL> select * from a where b like 11;

A           B
-- ----------
a          11

Quote:> SYBASE
> ======
> a  b
> -- -----------
> a  11
> (1 row(s) affected)

> Msg 11021, Level 16, State 2
> Function LIKE invoked with wrong number or type of argument(s).

> --
> http://www.cooper.com.hk
> http://sybooks.sybase.com/onlinebooks
> http://www.sybase.com.hk

--
Regards,

Mark Townsend

Principal Product Manager                       Ph:     (650) 633 5764
Server Division                                 Fx:     (650) 506 7222

  mtownsen.vcf
< 1K Download
 
 
 

LIKE comparisons

Post by C. Ferguso » Wed, 22 Mar 2000 04:00:00


Hi,
  read up on char and varchar2 in oracle.
  you have specified a column of char(2) which to Oracle is a fixed length
column.
  The value that is stored after your inserting 'a' is 'a ', ('a' with white
space padding to bring it out to the fixed length of 2).

  So, to get a return,
   select * from a where c like 'a%' will get you the the row your inserted.

  As I mentioned earlier,  read up on char and varchar datatypes in Oracle.

regards,
Cindy


> Same result after changing the column name:

> create table a (c char(2), b int)
> ;
> insert into a values ('a',11)
> ;
> select * from a where c like 'a'
> ;

> C  B
> -- ----------
> 0 rows selected.


> > Most likely the first oracle query suffers from a scoping problem: bad
> > column and table naming.
> > I would recommend NOT using column names identical to the table name before
> > you start posting about incorrect results.
> > Regards,

> > Sybrand Bakker, Oracle DBA



> >> I found that for the same SQL like statement, different databases return
> >> different results.  I don't know which vendor is correct or more ANSI
> > standard.
> >> For example,

> >> create table a (a char(2), b int)
> >> insert into a values ('a',11)
> >> select * from a where a like 'a'
> >> select * from a where b like 11

> >> MS-SQL
> >> ======
> >> a    b
> >> ---- -----------
> >> a    11
> >> (1 row(s) affected)
> >> a    b
> >> ---- -----------
> >> a    11
> >> (1 row(s) affected)

> >> ORACLE
> >> ======
> >> A  B
> >> -- ----------
> >> 0 rows selected.
> >> A  B
> >> -- ----------
> >> a          11
> >> 1 row selected.

> >> SYBASE
> >> ======
> >> a  b
> >> -- -----------
> >> a  11
> >> (1 row(s) affected)

> >> Msg 11021, Level 16, State 2
> >> Function LIKE invoked with wrong number or type of argument(s).

> >> --
> >> http://www.cooper.com.hk
> >> http://sybooks.sybase.com/onlinebooks
> >> http://www.sybase.com.hk

> --
> http://www.cooper.com.hk
> http://sybooks.sybase.com/onlinebooks
> http://www.sybase.com.hk

 
 
 

LIKE comparisons

Post by wildpon » Wed, 22 Mar 2000 04:00:00


if your using like then you should be using a wildcard % or just use = ,
and you might want to check to see if the actual data entry is caps or not

Oracle/DB2/SQL Server DBA


> Same result after changing the column name:

> create table a (c char(2), b int)
> ;
> insert into a values ('a',11)
> ;
> select * from a where c like 'a'
> ;

> C  B
> -- ----------
> 0 rows selected.


> > Most likely the first oracle query suffers from a scoping problem: bad
> > column and table naming.
> > I would recommend NOT using column names identical to the table name before
> > you start posting about incorrect results.
> > Regards,

> > Sybrand Bakker, Oracle DBA



> >> I found that for the same SQL like statement, different databases return
> >> different results.  I don't know which vendor is correct or more ANSI
> > standard.
> >> For example,

> >> create table a (a char(2), b int)
> >> insert into a values ('a',11)
> >> select * from a where a like 'a'
> >> select * from a where b like 11

> >> MS-SQL
> >> ======
> >> a    b
> >> ---- -----------
> >> a    11
> >> (1 row(s) affected)
> >> a    b
> >> ---- -----------
> >> a    11
> >> (1 row(s) affected)

> >> ORACLE
> >> ======
> >> A  B
> >> -- ----------
> >> 0 rows selected.
> >> A  B
> >> -- ----------
> >> a          11
> >> 1 row selected.

> >> SYBASE
> >> ======
> >> a  b
> >> -- -----------
> >> a  11
> >> (1 row(s) affected)

> >> Msg 11021, Level 16, State 2
> >> Function LIKE invoked with wrong number or type of argument(s).

> >> --
> >> http://www.cooper.com.hk
> >> http://sybooks.sybase.com/onlinebooks
> >> http://www.sybase.com.hk

> --
> http://www.cooper.com.hk
> http://sybooks.sybase.com/onlinebooks
> http://www.sybase.com.hk

 
 
 

LIKE comparisons

Post by Norri » Thu, 23 Mar 2000 04:00:00


I am sorry.  I am using

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.0.0 - Production

And you are using Oracle8i Release 2.  Anyway, I would try Oracle8i 8.1.5


> This is a multi-part message in MIME format.
> --------------C921136B2E61FE1883BC31F8
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
> Comments inline
> These aren't the results I get. See below
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> With the Partitioning option
> JServer Release 8.1.6.0.0 - Production
> SQL> create table a (a char(2), b int);
> Table created.
> SQL> insert into a values ('a',11);
> 1 row created.
> SQL> select * from a where a like 'a';
> A           B
> -- ----------
> a          11
> SQL> select * from a where b like 11;
> A           B
> -- ----------
> a          11
>> SYBASE
>> ======
>> a  b
>> -- -----------
>> a  11
>> (1 row(s) affected)

>> Msg 11021, Level 16, State 2
>> Function LIKE invoked with wrong number or type of argument(s).

>> --
>> http://www.cooper.com.hk
>> http://sybooks.sybase.com/onlinebooks
>> http://www.sybase.com.hk
> --
> Regards,
> Mark Townsend

> Principal Product Manager                  Ph:     (650) 633 5764
> Server Division                                    Fx:     (650) 506 7222

> --------------C921136B2E61FE1883BC31F8
> Content-Type: text/x-vcard; charset=us-ascii;
>  name="mtownsen.vcf"
> Content-Transfer-Encoding: 7bit
> Content-Description: Card for Mark Townsend
> Content-Disposition: attachment;
>  filename="mtownsen.vcf"
> begin:vcard
> n:Townsend;Mark
> tel;fax:(650) 506 7222
> tel;home:(650) 558 1379
> tel;work:(650) 633 5764
> x-mozilla-html:FALSE
> url:www.oracle.com
> org:Oracle Corporation;Server Division
> version:2.1

> title:Principal Product Manager
> adr;quoted-printable:;;4op626=0D=0A400 Oracle Parkway;Redwood City;CA;94065;USA
> x-mozilla-cpt:;-1
> fn:Mark Townsend
> end:vcard
> --------------C921136B2E61FE1883BC31F8--

--
http://www.cooper.com.hk
http://sybooks.sybase.com/onlinebooks
http://www.sybase.com.hk
 
 
 

1. Query slow over liked server

Hi,

I have a problem with a query runing on a linked server.

When I execute the following on the target server the query takes about 2
secs
UPDATE TableName
SET MarkedForXfer = 0
WHERE MarkedForXfer = 1

When I execute the following on a linked server from another server the
query takes about 30 mins
UPDATE SQLLinkedServer.Database.dbo.TableName
SET MarkedForXfer = 0
WHERE MarkedForXfer = 1

Has anyone got any Ideas why this would be?

Thanks
Devron

2. Lantastic

3. Counting the number of satisfied LIKES in a SELECT statement

4. ORACLE TESTS!!

5. Liked Server to SYBASE

6. Formatting a float of a DB grid

7. Visual Liking in .Net using TREE VIEW control

8. Concerning about licensing costs

9. Mulitiple LIKEs or IN?

10. Liked Server with insert trigers

11. Att: Bryant Likes - Virtual Directory stop to respond

12. function likes sprintf

13. Likes CAT and Sport Query