Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    18

    Unanswered: PostGreSQL with Regex

    Hi All

    I'm trying to write a case statement which examines if a user has correctly or incorrectly put in comments into a system based on a standard compliance manual. Im trying to do this with Regex

    The user is supposed to input it something like X OF Y
    so for example '15 of 20' where 15 is the part they are on and 20 is the total number of parts associated with the equipment. The 'of' portion of the text can be upper or lower case

    I'm using a CASE statement something along the lines of

    Code:
    CASE WHEN WHEN remark \d ~*[O]~*[F] THEN 'Compliant'
    Obviously this is very wrong. I have checked around Google and the forums but its not very clear to me in the explanations i have found

    Any help would be greatly appreciated

    Thanks for your time

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You only need the ~* operator only once and need to put the whole regex into single quotes:

    Code:
     CASE 
        WHEN remark ~* '\d+\s+of\s+\d+' THEN 'Compliant'
        else 'Not compliant'
     end as compliant_status
    The regex only accepts patterns that have at least one digit followed by at least one whitespace, followed by "of" followed by at least one whitespace followed by at least one digit.
    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

  3. #3
    Join Date
    Feb 2013
    Posts
    18
    Thank you, this is exactly what I'm looking for

Posting Permissions

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