• 15. Re: Introduction to regular expressions ... last part.
      572471
      Or using just one regex function and LENGTH:
      WHERE REGEXP_LIKE(num, '^[10]*(\(!\))?[10]*$') AND LENGTH (num) IN (5, 8)
      it would't be correct:
      SQL> with t as (select '10010110' num from dual)
        2  select num from t
        3  WHERE REGEXP_LIKE(num, '^[10]*(\(!\))?[10]*$') AND LENGTH (num) IN (5, 8)
        4  /
      
      NUM
      --------
      10010110
      • 16. Re: Introduction to regular expressions ... last part.
        BluShadow
        select t.*, case
                    when length(num) <= 8 then
                      regexp_substr(num, '^([10]{5}|[10]*\(!\)[10]*)$')
                    else null end as num2
          from t
        • 17. Re: Introduction to regular expressions ... last part.
          cd_2
          I'm afraid you need at least to regex functions, since there's also a length check involved. Is this solution getting close to your requirements?
          SELECT t.*
               , REGEXP_SUBSTR(REGEXP_SUBSTR(num, 
                                             '^(.){5,8} (.){3,6}$'), 
                               '^[10]*(\(!\))?[10]* [10]*(\(>\))?[10]*$')
            FROM t
          ;
          C.

          Changed everything to regexp_substr

          Message was edited by:
          cd
          • 18. Re: Introduction to regular expressions ... last part.
            BluShadow
            I'm afraid you need at least to regex functions,
            since there's also a length check involved.
            Ah, the joy of the CASE statement. ;)
            • 19. Re: Introduction to regular expressions ... last part.
              cd_2
              Not working with the new requirements.

              C.
              • 20. Re: Introduction to regular expressions ... last part.
                572471
                it's not long lasting joy :))
                cause it would be difficult tou use case in the provided example:
                Re: Introduction to regular expressions ... last part.
                • 21. Re: Introduction to regular expressions ... last part.
                  572471
                  cd

                  it's a pitty but your query also doesn't cover all the requirments:
                  SQL> with t as (select '100101 101(>)' num from dual)
                    2  SELECT t.*
                    3       , REGEXP_SUBSTR(REGEXP_SUBSTR(num,
                    4                                     '^(.){5,8} (.){3,6}$'),
                    5                       '^[10]*(\(!\))?[10]* [10]*(\(>\))?[10]*$')
                    6    FROM t
                    7  /
                  
                  NUM           REGEXP_SUBSTR(REGEXP_SUBSTR(NU
                  ------------- ------------------------------
                  100101 101(>) 100101 101(>)
                  • 22. Re: Introduction to regular expressions ... last part.
                    cd_2
                    Take a closer look at the solution with 2 regexp_substr functions.

                    Dang, missed it again.

                    C.

                    Message was edited by:
                    cd
                    • 23. Re: Introduction to regular expressions ... last part.
                      BluShadow
                      Not working with the new requirements.
                      Jeez, if people can't put up their full requirements at the start then I can't be bothered to help any more. Can't be doing with people chopping and changing what they really want, some of us have work to do and aren't just here to play games.

                      I'll leave this in cd's capable hands.
                      • 24. Re: Introduction to regular expressions ... last part.
                        572471
                        yep,
                        that's exactly what I did.
                        But it doesn't cover:
                        in the first block 5 symbols of [01] in the second block 3 symbols of [01].
                        In the first block it is optional to meet one (!), in the second block it is optional to meet one (>).
                        so it is possible to have 6-8 symbols of [01] in the first block without (!), and 4-6 symbols in the second block without (>).
                        • 25. Re: Introduction to regular expressions ... last part.
                          572471
                          Not working with the new requirements.
                          Jeez, if people can't put up their full requirements
                          at the start then I can't be bothered to help any
                          more. Can't be doing with people chopping and
                          changing what they really want, some of us have work
                          to do and aren't just here to play games.

                          I'll leave this in cd's capable hands.
                          IMHO, you're not right.
                          I just tried to simplify the real problem, but didn't cover all the requirments (that's my mistake I agree, my example wasn't representative). If you had shown how to do this with only regexp_substr without any case length, I wouldn't ask anything more.
                          But just imaging what will happen if people place here all the real data from tables and codes whithout any simplifying what it would look like?

                          If you don't have any more time to help me - that's ok.
                          Thanks anyway.
                          • 26. Re: Introduction to regular expressions ... last part.
                            cd_2
                            I'll leave this in cd's capable hands.
                            Yeah, go ahead and leave me in my misery. ;-)

                            @Volder: How about this?
                            SELECT t.*
                                 , REGEXP_SUBSTR(REGEXP_SUBSTR(num, 
                                                               '^([10]{5}|[10(!)]{8}) ([10]{3}|[10(>)]{6})$'),
                                                 '^([10]{5}|[10]*\(!\)[10]*) ([10]{3}|[10]*\(>\)[10]*)$')             
                              FROM t   
                            ;
                            C.
                            • 27. Re: Introduction to regular expressions ... last part.
                              121256
                              in the first block 5 symbols of [01] in the second block 3 symbols of [01].
                              In the first block it is optional to meet one (!), in the second block it is optional to meet one (>).
                              select * from t
                                where regexp_like(num,
                                  '^(((\(!\))?[01]{5})|([01](\(!\))?[01]{4})|([01]{2}(\(!\))?[01]{3})|([01]{3}(\(!\))?[01]{2})|([01]{4}(\(!\))?[01])|([01]{5}(\(!\))?))'
                                ||' (((\(>\))?[01]{3})|([01](\(>\))?[01]{2})|'||                                              '([01]{2}(\(>\))?[01])|([01]{3}(\(>\))?))$')
                              ;
                              
                              NUM
                              ----------------
                              10(!)010 10(>)1
                              (!)10010 011
                              10010(!) 101
                              10010 100(>)
                              
                              4 rows selected.
                              or just
                                  '^((\(!\))?[01]{5}|[01](\(!\))?[01]{4}|[01]{2}(\(!\))?[01]{3}|[01]{3}(\(!\))?[01]{2}|[01]{4}(\(!\))?[01]|[01]{5}(\(!\))?)'
                                ||' ((\(>\))?[01]{3}|[01](\(>\))?[01]{2}|'||                                          '[01]{2}(\(>\))?[01]|[01]{3}(\(>\))?)$')
                              Message was edited by:
                              Elic
                              • 28. Re: Introduction to regular expressions ... last part.
                                cd_2
                                I knew someone would come up with a solution that would include all possible combinations. ;-)

                                C.
                                • 29. Re: Introduction to regular expressions ... last part.
                                  572471
                                  @Volder: How about this?
                                  Awesome!!!
                                  cd, that's exactly what I was looking for :))
                                  Thanks a lot.