Question about LIKE operator

kokokoy · #1 (**permalink**) 05-10-10, 00:05

I have a problem in my project.

I have to retrive all data which start with 11. (for example 11,112,1156)
but the provided data type of this column is INTEGER.

I tried to use LIKE but it didn't work.

Can anyone suggest me how to solve this problem?

umayer · #2 (**permalink**) 05-10-10, 01:49

Try:

... WHERE VARCHAR(your_column) LIKE '11%'

dav1mo · #3 (**permalink**) 05-10-10, 08:53

what about leading 0's when converting datatype? you'll need to strip them out as well.
Dave

Lenny77 · #4 (**permalink**) 05-10-10, 12:45

Quote:

Originally Posted by dav1mo

what about leading 0's when converting datatype? you'll need to strip them out as well.
Dave

You don't need to worry about leading 0's if you are using the VARCHAR function.

Lenny

wilsonfv · #5 (**permalink**) 05-10-10, 13:34

Quote:

Originally Posted by Lenny77

You don't need to worry about leading 0's if you are using the VARCHAR function.

Lenny

As far as I know, there is not existing a VARCHAR function that accept integer type.

Stealth_DBA · #6 (**permalink**) 05-10-10, 14:04

wilsonfv, it depends and the version of DB2.

DB2 LUW V8.2 and 9.5, VARCHAR function does not accept integer as a parameter.

DB2 LUW V9.7, DB2 z/OS V8, and V9, VARCHAR funcion does accept integer as a parameter.

tonkuma · #7 (**permalink**) 05-10-10, 16:28

CHAR(integer-expression) is accepted more widely,
including DB2 LUW V8.2 to 9.7, DB2 z/OS V7 to V9, DB2 iSeries V5R2 to V6R1.

CHAR(integer-expression) would be padded with trailing blanks.
But, it would be no matter using in

... WHERE CHAR(your_column) LIKE '11%'

Lenny77 · #8 (**permalink**) 05-10-10, 18:19

Quote:

Originally Posted by tonkuma

CHAR(integer-expression) is accepted more widely,
including DB2 LUW V8.2 to 9.7, DB2 z/OS V7 to V9, DB2 iSeries V5R2 to V6R1.

CHAR(integer-expression) would be padded with trailing blanks.
But, it would be no matter using in

... WHERE CHAR(your_column) LIKE '11%'

Agree with you, tonkuma ! We can use Char as well as Varchar.

But if your_column is first column of index using function can affect performance.

Lenny

Lenny77 · #9 (**permalink**) 05-10-10, 18:33

Instead of LIKE expression you can use the query with much better performance:

Code:

with power(pwr) as
(select 1 from sysibm.sysdummy1
 union all
select pwr + 1
  from power
where pwr <= 7) 
select * from 
your_table join power
On 
your_column between 11 * power(10, pwr) and 12 * power(10, pwr + 1) - 1
or your_column  = 11

Lenny

DB2Plus · #10 (**permalink**) 05-10-10, 22:55

Quote:

Originally Posted by Lenny77

Instead of LIKE expression you can use the query with much better performance:

Code:

with power(pwr) as
(select 1 from sysibm.sysdummy1
 union all
select pwr + 1
  from power
where pwr <= 7) 
select * from 
your_table join power
On 
your_column between 11 * power(10, pwr) and 12 * power(10, pwr + 1) - 1
or your_column  = 11

Lenny

Lenny, your query consists the small mistake. Let me correct it:

Code:

with power(pwr) as
(select 0 from sysibm.sysdummy1
 union all
select pwr + 1
  from power
where pwr <= 7
)
select * from 
your_table join power
On 
your_column between 11 * power(10, pwr) and 12 * power(10, pwr) - 1

Thanks, Kara.

Internet Services

Web Development

Digital Marketing

Consumer Tech