[SOLVED] using two fields with like

[isaac_cm]

I am trying to make the where clues like this

 

where (f1 + f2 like "%red%")

 

the problem it is working only if I write any value is part of field f1 and never return any record if I write value from field f2

 

any Ideas ?

 

Thanks

[teng84]

where f1 like '%red%' or f2 like '%red%'

better i guess and easier

[isaac_cm]

do you think I can use concat function ?

[Barand]

if f1 contains "butter" and f2 contains "edam", do you want to include that record in the results?

[isaac_cm]

I  just want if I write the word "am"  I get that record because it is part of f2, and why plus sign "+" dont work !!!

[Barand]

"+" is arithmetic addition operator.

 

[pre]

  f1    |    f2    |

---------+------------+

  ham    |    x      |

  y    |    spam    |

  culpa  |    mea    |

[/pre]

 

  
SELECT * WHERE CONCAT(f1, f2) LIKE '%am%'

will return all 3, if that is the result you want.

 

Teng's version

SELECT * WHERE f1 LIKE '%am%' OR  f2 LIKE '%am%'

will return the first 2.

 

Depends on how you want it

[isaac_cm]

Thanks to all of you

 

I will use the Teng's version because not the two fields are one word

 

and I think I know why + work only with f1 not f2 I think the query return ascii code numbers of the characters in f2 because of using +, right ?.

[Barand]

If you

 

SELECT (f1 + f2) as x FROM tablename

 

you can see what it returns

[isaac_cm]

I got some records contains only zeros

[fenway]

I got some records contains only zeros

 

What?

[isaac_cm]

hello fenway

I have my table like that

 

main(varchar)  sub(varchar)        result of using "+"

------------  ---------------    ------------------

  10                      Red                  10

  10                      Blue                  10

  12                      Green                12

  Red                      null                  0

  Blue                    null                    0

  White                  null                    0

 

 

as you see I get zeros !!

[fenway]

So why use +?

[isaac_cm]

I thought I could use + to search inside the two fields like that (f1 + f2 like "%red%") however I understand now it wont work