Decode, can I use it?

I have to rewrite MySQL query that uses FIND_IN_SET function for Oracle.
I have in one field values comma separated, like this "23,214,24" and I need to select rows where for exapmle 214 is present in set. In MySQL I would do like this "SELECT * FROM TABLE WHERE FIND_IN_SET("214",FIELDNAME).
What can i use in ORACLE instead? Can I used decode somehow?

You can use 'like' function
Select * from table where mycol like '%214%';

-D

Quote:

---

Originally Posted by daljitsb

You can use 'like' function
Select * from table where mycol like '%214%';

-D

---

I know that I can use it, but that's not the way out :) and besides you have to combine "mycol like '214%' or mycol like '%214' or mycol like '%,214,%'or mycol = '214'"
tahnks anyway

No you don't... just like '%214%' as daljitsb said.

Select * from table where ','||mycol||',' like '%,214,%';
is probably better as
Select * from table where mycol like '%21%' could match against "211, 121" etc

gamyers -- Your solution is pretty creative but it doesn't work when 214 is the first or the last number in the list... I'll stick with mycol like '%214%'

It works fine, because I've added the commas before and after mycol.

create table mt (mycol varchar2(200));

Table created.

insert into mt values ('214');

1 row created.

Select * from mt where ','||mycol||',' like '%,214,%';

MYCOL
----------------------------------------------------------------------------------
214

Quote:

---

Originally Posted by VTV

I have in one field values comma separated, like this "23,214,24"

---

Congrats!... you solved a problem VTV doesn't have. You have to read the original specs

PABV,
If the value he wanted to search for was '2', your "mycol like '%2%'" solution would incorrectly say that "23,214,24" did contain the value.
My solution would correctly say that it did not.
Your solution works correctly for the example he gave, but is not correct as a general solution.

Quote:

---

Originally Posted by gamyers

... works correctly for the example he gave

---

There is just one thing you cannot alter which is the original specs. Original post got "posted" 'cause of the original specs :cool:

Ahh...so your concept of meeting the specification is that it gets the result right for a single EXAMPLE query. If which case, you may as well simply hard code the result in and not run any SQL at all.
Or perhaps you simply don't understand the word "example".
Just face it, your code DOES NOT WORK. It is WRONG. It is INADEQUATE. It is a FAILURE.

This is not going any place. Have a nice life pal.

Quote:

---

Originally Posted by PAVB

This is not going any place. Have a nice life pal.

---

Your answer was wrong.