This discussion is archived
6 Replies Latest reply: Mar 5, 2013 10:20 AM by Solomon Yakobson RSS

Remove nNumber of charters and punctuation if exits

GMoney Newbie
Currently Being Moderated
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
    Justin Cave Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Does this leave the ',' in either situation? I need the comma (if it is there) to be stripped out as well.

    thanks

Legend

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