No replies to this topic

[Eric The Red]

What I am trying to do is find a numerical string inside a large comma delimited string.

What I am using is this:

SELECT COUNT(id) AS numa FROM property_emailed WHERE ((prop_nums LIKE  '%,:prop_num,%') OR (prop_nums LIKE '%,:prop_num') OR (prop_nums LIKE  ':prop_num,%') OR (prop_nums LIKE ':prop_num')) AND (date_sent BETWEEN  ':t1' AND ':t2')

and this works. But one of the things I was trying was using CASE or IF statements and I could not get them to work for the life of me.

One of the attempts looked like this:

SELECT * FROM property_emailed;
SELECT prop_nums,
CASE WHEN length('prop_nums') <=3 THEN 
SELECT COUNT(id) AS numa FROM property_emailed WHERE (prop_nums LIKE  ':prop_num') AND (date_sent BETWEEN ':t1' AND ':t2')
WHEN length('prop_nums') >=4 THEN 
SELECT COUNT(id) AS numa FROM property_emailed WHERE (prop_nums LIKE  ':prop_num,%') OR (prop_nums LIKE '%,:prop_num,%') OR (prop_nums LIKE  '%,:prop_num') AND (date_sent BETWEEN ':t1' AND ':t2')
END;

I also tried this

SELECT * FROM property_emailed
IF length('prop_nums') <=3 THEN 
SELECT COUNT(id) AS numa FROM property_emailed WHERE (prop_nums LIKE  ':prop_num') AND (date_sent BETWEEN ':t1' AND ':t2')
ELSE length('prop_nums') >=4 THEN 
SELECT COUNT(id) AS numa FROM property_emailed WHERE (prop_nums LIKE  ':prop_num,%') OR (prop_nums LIKE '%,:prop_num,%') OR (prop_nums LIKE  '%,:prop_num') AND (date_sent BETWEEN ':t1' AND ':t2')
END IF;

Could someone please tell me what I was doing wrong in those two instances so I can better understand how to do IF and CASE statements? Both of those versions gave errors.

Thanks in advance!