Decode, can I use it?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Decode, can I use it?

  1. #1
    Join Date
    Mar 2007
    Posts
    2

    Lightbulb 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?

  2. #2
    Join Date
    Jul 2003
    Posts
    136
    You can use 'like' function
    Select * from table where mycol like '%214%';

    -D

  3. #3
    Join Date
    Mar 2007
    Posts
    2
    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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Feb 2005
    Posts
    158
    Select * from table where ','||mycol||',' like '%,214,%';
    is probably better as
    Select * from table where mycol like '%21%' could match against "211, 121" etc

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  7. #7
    Join Date
    Feb 2005
    Posts
    158
    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

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    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.

  9. #9
    Join Date
    Feb 2005
    Posts
    158
    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.

  10. #10
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    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
  •  


Click Here to Expand Forum to Full Width