Wildcards in DB2 SQL

[shubhangi goel]

Hi,

I am new to this forums and new to DB2 too.

I hav a query.
My table is

Name
ajay
shubhangi
kunal

when i am executing the query
select name from table_name where name like '%i'
answer coming is 0 record selected instead of shubhangi.

another query
select name from table_name where like 's%i'
again answer coming is 0 record.
according to me it should be shubhangi

can anyone tell me wats wrong with it or help me write a query dat start with s and end with i.
Any help is highly appreciated.

Thanks,
shubhangi

[vijay2082]

Hi,

Works perfectly for me as designed. Look at below set of steps to see what could be going wrong in your case.

C:\backup>db2 create table db2admin.mytable(name varchar(30))
DB20000I The SQL command completed successfully.

C:\backup>db2 "insert into db2admin.mytable values('ajay')
DB20000I The SQL command completed successfully.

C:\backup>db2 "insert into db2admin.mytable values('shubhangi')"
DB20000I The SQL command completed successfully.

C:\backup>db2 "insert into db2admin.mytable values('kunal')"
DB20000I The SQL command completed successfully.

C:\backup>db2 commit
DB20000I The SQL command completed successfully.

C:\backup>db2 select * from db2admin.mytable

NAME
------------------------------
ajay
shubhangi
kunal

3 record(s) selected.


C:\backup>db2 "select name from db2admin.mytable where name like '%i'"

NAME
------------------------------
shubhangi

1 record(s) selected.

C:\backup>db2 "select name from db2admin.mytable where name like 's%i'"

NAME
------------------------------
shubhangi

1 record(s) selected.

> Let us try inserting a name in upper case as below and see what happens to next query.

C:\backup>db2 "insert into db2admin.mytable values('SHUBHANGI')"
DB20000I The SQL command completed successfully.

C:\backup>db2 select * from db2admin.mytable

NAME
------------------------------
ajay
shubhangi
kunal
SHUBHANGI

4 record(s) selected.


> Below query still returns the odl result as expected
C:\backup>db2 "select name from db2admin.mytable where name like 's%i'"

NAME
------------------------------
shubhangi

1 record(s) selected.


> Below query returns both entry when we do a case insensitive search

C:\backup>db2 "select name from db2admin.mytable where lower(name) like 's%i'"

NAME
------------------------------
shubhangi
SHUBHANGI

2 record(s) selected.


Cheers, Vijay