8 Replies Latest reply: Mar 19, 2013 7:32 AM by user260user RSS

    features characters

    user260user
      hello
      I wanted to ask you politely.
        In this table:

      create table znaki (
      text_ varchar2(300)
      )


      and by filling the data:

      insert into test1 values ('aaa{bbb');
      insert into test1 values ('{mmmm');
      insert into test1 values ('oooo{');
      insert into test1 values ('zzzzzzzz');
      insert into test1 values ('ww{kk{uuu');

      how to get such a result:
      aaa
      oooo
      zzzzzzzz
      ww{kk


      Notch marks to encounter {                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
        • 1. Re: features characters
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: features characters
            Frank Kulash
            Hi,

            So, you want the text before (but not including) the last '{', but if there is no '{', then you want the full text. Is that it?

            Here's one way:
            SELECT  NVL ( SUBSTR ( text_
                                 , 1
                                 , INSTR (text_, '{', -1) -1
                                 )
                        , text_
                        )   AS before_last_bracket
            FROM    znaki;
            • 3. Re: features characters
              user260user
              thank you very much for the help:

              returns me the result:
              BEFORE_LAST_BRACKET
              {mmmm                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
              oooo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
              zzzzzzzz                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
              ww{kk       

              I have one question. How do I perform to check the first character?

              Changing parameters of the function, the negative to positive values, but it did not help.
              the record:

              {mmmm

              to be in the form of
              null

              How can I get?                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
              • 4. Re: features characters
                Frank Kulash
                Hi,

                Sorry, I don't understand what you want.


                Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
                Explain, using specific examples, how you get those results from that data.
                Post your complete query, and point out where it is not producing the right results from your sample data.
                Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
                See the forum FAQ {message:id=9360002}
                • 5. Re: features characters
                  Manik
                  Completely relying on what you posted....Here is one way to achive your answer.
                  SELECT NVL2 (NULLIF (INSTR (text_, '{'), 0),
                               SUBSTR (text_, 1, INSTR (text_, '{', -1) - 1),
                               text_)
                            val
                    FROM znaki
                   WHERE (text_ NOT LIKE '{%');
                  OR

                  Simpler version:
                  SELECT DECODE (INSTR (text_, '{'),
                                 0, text_,
                                 SUBSTR (text_, 1, INSTR (text_, '{', -1) - 1))
                            val
                    FROM znaki
                   WHERE (text_ NOT LIKE '{%');
                  Both the queries yield:

                  output:
                  VAL
                  ----------
                  aaa
                  oooo
                  zzzzzzzz
                  ww{kk
                  Cheers,
                  Manik.

                  Edited by: Added another easier approach.
                  • 6. Re: features characters
                    jeneesh
                    And the REGEXP one, for your reference.. REGEXP functions are more CPU intensive..So, they can be less performing if your data volume is huge..
                    SQL> select text_,regexp_replace(text_,'\{[^\{]*$') text_new
                      2  from test1;
                    
                    TEXT_                     TEXT_NEW
                    ------------------------- -------------------------
                    aaa{bbb                   aaa
                    {mmmm
                    oooo{                     oooo
                    zzzzzzzz                  zzzzzzzz
                    ww{kk{uuu                 ww{kk
                    {mmmm
                    
                    6 rows selected.
                    • 7. Re: features characters
                      chris227
                      user13387916 wrote:
                      I have one question. How do I perform to check the first character?

                      Changing parameters of the function, the negative to positive values, but it did not help.
                      the record:
                      Just change -1 to 1 ( oor leave it out as this is the default)
                      SELECT  SUBSTR ( text_
                                           , 1
                                           , INSTR (text_, '{') -1
                                           )
                                  AS before_last_bracket
                      FROM    znaki;
                      • 8. Re: features characters
                        user260user
                        Thank you very much for all your help. :) :) :)

                        Operate the solution given by You