-1

I am using Oracle 11g.

I am storing multiple values in a column separated by delimiter(#@#). I know it is wrong database design but since it is used for many years i can't do much about it. I need to perform a search operation where i pass multiple values and i should get all the records if any one value match within the stored value. I read about regular expressions for such scenarios. Can someone guide me how to proceed with this? Thanks in advance.

Thanks

Prabhakar
  • 1
  • can you give us some sample data and table layouts? if you can I would prefer it if you could put it in a http://sqlfiddle.com/ – Jeremy C. Jun 01 '15 at 14:16
  • in MSSQL if i know a max number of possible search terms I wld use `WHERE (Col LIKE '%#@#'+SearchTerm1 +'#@#%' OR SearchTerm1 Is NULL) AND (Col LIKE '%#@#'+SearchTerm2 +'#@#%' OR SearchTerm2 Is NULL)...` .. for unknown numbers of search terms then i would populate a temp table jand join to it.. `SELECT * FROM YourTable AS A INNER JOIN #STerms AS B on A.Col LIKE '%#@#'+B.STerm+'#@#%'` .. –  Jun 01 '15 at 15:11
  • note my answer is making assumptions about how you have used delimiters.. e.g. if you don't use delimiters when you have only one value then you need to think about how you want to handle the various scenarios as they will all have their weaknesses depending on what values you are storing there. –  Jun 01 '15 at 15:14
  • http://sqlfiddle.com/#!9/9273b/1/0 Please refer this link... I am expecting all the records where Portfolio1 is there. – Prabhakar Jun 01 '15 at 17:51
  • 1
    maybe i'm not understanding what you are trying to do but can't you just query with like? select * from projects where portfolio like '%Portfolio1%' – Jared Jun 01 '15 at 19:58
  • I have to use it in where clause of the query. And also multiple values will be passed as search string. – Prabhakar Jun 02 '15 at 15:52

1 Answers1

0

Modification of this:

WITH tab AS (SELECT 'ab#@#bc#@#cd#@#de#@#ef' col FROM DUAL)
SELECT subcol
  FROM (    SELECT REGEXP_SUBSTR (col,
                                  '[^#@#]+',
                                  1,
                                  LEVEL)
                      subcol
              FROM tab
        CONNECT BY REGEXP_SUBSTR (col,
                                  '[^#@#]+',
                                  1,
                                  LEVEL)
                      IS NOT NULL)
 WHERE REGEXP_LIKE (subcol, 'c|d');

The inner select builds a table of all values separated by #@#. In your app you build the search term list with '|' as separator and pass it eg. like a bind variable.

Community
  • 1
  • 1
Jon Tofte-Hansen
  • 794
  • 6
  • 16
  • I have added few more rows in the table for testing. I am not sure whether i am searching in right way. Please have a look at this sqlfiddle demo: http://sqlfiddle.com/#!4/a27058/2 – Prabhakar Jun 02 '15 at 16:40
  • I have made a new version at http://sqlfiddle.com/#!4/73094/10. Have added a distinct and a lower() to serve what seems to be your needs. You will have to test it thoroughly if you need it in production though. – Jon Tofte-Hansen Jun 03 '15 at 08:07