Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    1

    Unanswered: Using Like to check against a multivalue string

    Hi All --

    I am trying to write my query like this:

    select fields from table t where someField = 'someThing'
    and ', '|| t.REGION || ', ' like '%, ' || 'abc' ||', %'

    But this only works if I am comparing a single value. If my string has multiple values, this qry returns no results.

    my string can contain multiple values - For Ex: 'abc, def, xyz, hys' or it can be a single value like 'abc'.

    Any ideas? I highly appreciate any help or ideas around this.

    Thanks Much!!

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    If you just want to validate the string('abc') exists in the column(region) then below are just a couple of examples:
    Code:
    select fields
     from  table
    where  somefield  =  'someThing'
     and   region like '%abc%'
    
    select fields
     from  table
    where  somefield  =  'someThing'
     and   instr( region, 'abc', 1, 1 ) > 0

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by its_me11 View Post
    But this only works if I am comparing a single value. If my string has multiple values, this qry returns no results.

    my string can contain multiple values - For Ex: 'abc, def, xyz, hys' or it can be a single value like 'abc'.

    Any ideas? I highly appreciate any help or ideas around this.
    Do not change your query. Fix your data model.

    Putting multiple values into a single column is a very, very bad idea. Don't do it.

    (Hint: search the web for the term "normalization").
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •