June 29, 2011 at 1:13 am
hi,
i need to fetch the records that are delimited by "comma" i.e., "," can any one help me out...here is the query
select cot.contentid from
content_tags cot,cms_tags ct where
ct.tagid = cot.tag_Id and ct.tagname like concat('%app1%','%app2%')
any help regarding this...
i tried like this
select cot.contentid,ct.tagname from
content_tags cot,cms_tags ct where
ct.tagid = cot.tag_Id and ct.tagname like concat_ws('%app1%','%app2%')
im getting the output for only "app2" but i need to get both "app1" and "app2"
June 29, 2011 at 1:32 am
I suspect this is Oracle SQL, isn't it?
-- Gianluca Sartori
June 29, 2011 at 1:36 am
no..im using mysql..can u help me out..
June 29, 2011 at 1:36 am
I'm not sure this is what you're after (you mention comma separated values in your post, but I don't see it in your code).
If you want to return all rows that match the pattern '%app1%' or the pattern '%app2%', this is the code:
SELECT cot.contentid,ct.tagname
FROM content_tags cot
INNER JOIN cms_tags ct
ON ct.tagid = cot.tag_Id
WHERE ct.tagname LIKE '%app1%' OR ct.tagname LIKE '%app2%'
If you want to achieve something different, please expand your problem description.
Hope this helps
Gianluca
-- Gianluca Sartori
June 29, 2011 at 1:42 am
in my application.. i'll enter values in text field i.e., "app1,app2,app3" for that what i mentioned is my query.From that text field im getting the values into query..can you suggest me how to write the query.
June 29, 2011 at 1:50 am
In my opinion, you should split the values on the application side and then use the tokens in a query such as the one I posted earlier.
If this was SQL Server, you could use a splitter function. I don't know if one exists in MySQL, but I guess there must be some good implementation of it around forums and blogs.
Have you tried with Google?
-- Gianluca Sartori
June 29, 2011 at 1:54 am
i tried in google...all the examples are given as use of concat in "select" statement..but i need to implement in "like" statement...
June 29, 2011 at 1:59 am
I just found this one that seems promising:
-- assign value to variable
SET input = 'app1,app2,app3';
-- create temporary table and fill it with splitted values
CREATE TEMPORARY TABLE temp (id TINYINT NOT NULL auto_increment, val CHAR(20), PRIMARY KEY(id));
SET input=REPLACE(input, ",", "'),('");
SET @dyn_sql=CONCAT("INSERT INTO temp (val) VALUES ('",input,"');");
PREPARE s1 FROM @dyn_sql; EXECUTE s1;
SELECT cot.contentid,ct.tagname
FROM content_tags cot
INNER JOIN cms_tags ct
ON ct.tagid = cot.tag_Id
WHERE EXISTS (
SELECT *
FROM temp
WHERE ct.tagname LIKE CONCAT(CONCAT('%', val),'%')
)
Disclaimer: totally untested.
-- Gianluca Sartori
June 29, 2011 at 2:11 am
i tried what u suggested me
select
contenttag8_.contentid ,cmstags9_.tagname
from
cms.content_tags contenttag8_,
cms.cms_tags cmstags9_
where
cmstags9_.tagid=contenttag8_.tag_id
and (
cmstags9_.tagname like concat(concat('%app1%','%app2%'))
)
im unable to get the o/p values..it is showing blank where the problem goes...unable to get the values
June 29, 2011 at 2:29 am
Your last SQL statement looks a lot like the first one. What have you tried of my suggestions?
However, this is a SQL Server forum, I'm sure you would get better support on a MySQL forum.
-- Gianluca Sartori
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply