6 Replies Latest reply: Mar 5, 2013 12:20 PM by Solomon Yakobson RSS

    Remove nNumber of charters and punctuation if exits

    GMoney
      I need some help with cleaning up an address list. I want to extract the city name from a string the looks like this BROOKLYN, MI 492303.
      My desired result would simply be:

      BROOKLYN

      I know this works, but if there is a comma(,) I need to remove it as well. (Removing 9 characters moving left.)
      select regexp_replace('BROOKLYN, MI 492303','.........$') FROM dual
      Which this removes the comma (,):
      SELECT REGEXP_REPLACE('BROOKLYN, MI 492303','([[:punct:]])') from dual
      How do I do both?

      thanks for looking.
        • 1. Re: Remove nNumber of charters and punctuation if exits
          JustinCave
          What is the algorithm you are trying to implement? If the first approach (removing the last 9 characters) is acceptable, you could always just remove the last 10 characters to remove the comma. It seems highly unlikely, though, that you can guarantee that the city is everything other than the last 10 characters of the string.

          What is it, exactly, that you are trying to return? The first query returns the string "BROOKLYN,". The second query returns the string "BROOKLYN MI 492303". Assuming you want to return "BROOKLYN", you could nest your queries
          SQL> ed
          Wrote file afiedt.buf
          
            1  SELECT regexp_replace(
            2           REGEXP_REPLACE('BROOKLYN, MI 492303',
            3                          '([[:punct:]])'),
            4           '.........$')
            5*   from dual
            6  /
          
          REGEXP_RE
          ---------
          BROOKLYN
          My guess, though, is that you really want to return everything before the first comma in which case you can do something like
          SQL> ed
          Wrote file afiedt.buf
          
            1  with x as (
            2    select 'BROOKLYN, MI 492303' str from dual
            3  )
            4  select substr( str, 1, instr( str, ',' )-1)
            5*   from x
          SQL> /
          
          SUBSTR(S
          --------
          BROOKLYN
          Justin
          • 2. Re: Remove nNumber of charters and punctuation if exits
            Frank Kulash
            Hi,
            GMoney wrote:
            I need some help with cleaning up an address list. I want to extract the city name from a string the looks like this BROOKLYN, MI 492303.
            My desired result would simply be:

            BROOKLYN

            I know this works, but if there is a comma(,) I need to remove it as well. (Removing 9 characters moving left.)
            select regexp_replace('BROOKLYN, MI 492303','.........$') FROM dual
            Which this removes the comma (,):
            SELECT REGEXP_REPLACE('BROOKLYN, MI 492303','([[:punct:]])') from dual
            How do I do both?

            thanks for looking.
            When you say, "I want 'BROOKLYN', you're inviting a response that happens to work on this 1 row of sample data, without regard for how it will work on all the data in your real table.

            Do you want the first 8 characters of the string? If so
            {code}
            SUBSTR (str, 1, 8)
            {code}

            Do you want the first 9 characters when the 9th character is not a comma, and the first 8 characters otherwise? If so:
            {code}
            SUBSTR (str, 1, 8) ||
            NULLIF ( SUBSTR (str, 9, 1)
            , ','
            )
            {code}

            Do you want everything before the 1st comma, or the whole string, if there is no comma? If so
            {code}
            SUBSTR ( str
            , 1
            , INSTR (str || ',', ',') - 1
            )
            {code}

            Regular expressions are available if you really need them, and if performance isn't an issue.
            As you can see, all of the things above can be done fairly simply without regular expressions.


             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data. You can probably give a good example with 5 or 10 rows in this case.
            Explain, using specific examples, how you get those results from that data.
            Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            • 3. Re: Remove nNumber of charters and punctuation if exits
              Solomon Yakobson
              SQL> select regexp_substr('BROOKLYN, MI 492303','^[^,]+,') from dual
                2  /
              
              REGEXP_SU
              ---------
              BROOKLYN,
              SQL> select substr('BROOKLYN, MI 492303',1,instr('BROOKLYN, MI 492303',',')) from dual
                2  /
              
              SUBSTR('B
              ---------
              BROOKLYN,
              SQL>  
              SY.
              • 4. Re: Remove nNumber of charters and punctuation if exits
                Solomon Yakobson
                I missed "if exists". Assuming words are separated by a space:
                select regexp_substr('BROOKLYN, MI 492303','^[^ ]+,') from dual
                /
                
                REGEXP_SU
                ---------
                BROOKLYN,
                SQL> 
                SY.
                • 5. Re: Remove nNumber of charters and punctuation if exits
                  GMoney
                  Exactly what I needed - to combine the 2 seperate regexp_replace and get rid of the ',' if there was one.

                  thanks!
                  • 6. Re: Remove nNumber of charters and punctuation if exits
                    GMoney
                    Does this leave the ',' in either situation? I need the comma (if it is there) to be stripped out as well.

                    thanks