-
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
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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%'
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|