Determining which column the query was found in

jasonyoung

I'm allowing a query which searches across multiple columns -- and it does work, but since this is a "wildcard" type search where the query (say "foo") could be in any or all of the columns searched, how do I determine which column the hit(s) was/were in. I need this so I when I jump into the for loop I row out the results properly?

The search is like:

SELECT uid,sslnumber,accountnumber,accountname,accountaddress FROM accounttracking 
WHERE accountnumber LIKE '%V04047%' || accountaddress LIKE '%V04047%' || accountphone 
LIKE '%V04047%' || ccsdongle LIKE '%V04047%' /// cut for brevity

The results are displayed in a table. See attachment for visual. What I'm after is adding the column where the match was made to the end of the output shown in the attachment. Make sense????

Thanks.

jasonyoung

Nobody knows how to do this??

I'm still trying, and still no luck!

HELLLLLLLPPPPPP!

Weedpacket

Moving to a more appropriate forum.

tomhath

SELECT uid,sslnumber,accountnumber,accountname,accountaddress
  case when  accountnumber LIKE '%V04047%' then 1 else 0 end as accountnumber_match,
  case when  accountaddress LIKE '%V04047%'  then 1 else 0 end as accountaddress _match,
  case when  accountphone LIKE '%V04047%'  then 1 else 0 end as accountphone _match,
  case when  ccsdongle LIKE '%V04047%'  then 1 else 0 end as ccsdongle _match
FROM accounttracking
WHERE accountnumber LIKE '%V04047%' || 
accountaddress LIKE '%V04047%' || 
accountphone LIKE '%V04047%' || 
ccsdongle LIKE '%V04047%'

jasonyoung

Thanks, I really appreciate you taking the time to help me out!!!

That got me 50% of the way there. The only thing additionally wanted would be for the columns to only show IF there is a match in it AND to show the value retrieved instead of a 1.

Can do?

jasonyoung

No can do? I can make it output anything I want but it's static. Can it be the returned value from the query?