This discussion is archived
8 Replies Latest reply: Mar 19, 2013 5:32 AM by user13387916 RSS

features characters

user13387916 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: features characters
    Frank Kulash Guru
    Currently Being Moderated
    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
    user13387916 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    user13387916 Newbie
    Currently Being Moderated
    Thank you very much for all your help. :) :) :)

    Operate the solution given by You

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points