Hello,

I have two tables..

The first has a column whith data like this:

A99999

the second has a column whith data like this:

XXX0000/Zxy_A99999_1234

I want to select data from the second table using the first table, something like this:

select * from second where second.column like '%first.column%'

Any sugestion how can i do that?

I'm using informix..

in ANSI SQL, you would use this --by the way, did you know we have an Informix forum?

I don't know what functions Informix has available but I would look for one that can locate a character string in another string.

In DB2 it would be something like:

WHERE LOCATE(COL1, COL2) > 0

This would search for the value of COL1 (A99999) in COL2(XXX0000/Zxy_A99999_1234) and return the starting position of that value. A 0 indicates it is not found so "> 0" indicates it was in the string. If you have that function it may be called something else.

ANSI/ISO SQL has POSITION:

<string position expression> ::=
POSITION <left paren> <string value expression>
IN <string value expression> <right paren>

e.g ...WHERE POSITION(COL1 IN COL2) > 0

tks man, worked!