Home » SQL & PL/SQL » SQL & PL/SQL » How to remove ordinal suffixes after the numbers (pl/sql developer, windows 10)
How to remove ordinal suffixes after the numbers [message #677186] Thu, 29 August 2019 09:03 Go to next message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
I am Trying to compare the following two strings in pl/sql. One string contains ordinal characters (th|rd|nd|st) following the numbers and other one is only numbers.
For Example

string1 = 42nd street, 42nd East, W 23rd, New York
string2 = 42 street, 42 East, W 23, New York

My question is how to remove the ordinal suffixes after the numbers in string1 and then compare it to the string2.

Please help me.
Thanks to all.
Re: How to remove ordinal suffixes after the numbers [message #677187 is a reply to message #677186] Thu, 29 August 2019 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 26606
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: How to remove ordinal suffixes after the numbers [message #677191 is a reply to message #677186] Thu, 29 August 2019 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you want to remove exactly (th|rd|nd|st) after a number then:
SQL> with data as (select '42nd street, 42nd East, W 23rd, New York' address from dual)
  2  select regexp_replace(address, '(\d)(th|rd|nd|st)', '\1') new_addre
  3  from data
  4  /
NEW_ADDRE
----------------------------------
42 street, 42 East, W 23, New York

If you want to remove any alphabetical character after a number then:
SQL> with data as (select '42nd street, 42nd East, W 23rd, New York' address from dual)
  2  select regexp_replace(address, '(\d)[a-z]+', '\1', 1, 0, 'i') new_addr
  3  from data
  4  /
NEW_ADDR
----------------------------------
42 street, 42 East, W 23, New York

Re: How to remove ordinal suffixes after the numbers [message #677194 is a reply to message #677191] Thu, 29 August 2019 11:49 Go to previous messageGo to next message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
Thank you so much Michel cadot for your quick response.

I have one more question is it correct or not.

unexpectedly i forget to give a space 42station
I try to with data as (select '42station street, 42nd East, W 23rd, New York' address from dual)
select regexp_replace(address, '(\d)(th|rd|nd|st)', '\1') new_addre from data

output as
42ation street, 42 East, W 23, New York

My question is there any possibility to print 42station street 42 East, W 23, New York
Re: How to remove ordinal suffixes after the numbers [message #677195 is a reply to message #677194] Thu, 29 August 2019 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Something like (saying the offending string is followed by a non word character or is at the end):
SQL> with data as (select '42station street, 42nd East, W 23rd, New York' address from dual)
  2  select regexp_replace(address, '(\d)(th|rd|nd|st)(\W|$)', '\1\3') new_addre from data
  3  /
NEW_ADDRE
-----------------------------------------
42station street, 42 East, W 23, New York

[Updated on: Thu, 29 August 2019 13:29]

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677198 is a reply to message #677195] Thu, 29 August 2019 14:22 Go to previous messageGo to next message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
Thank you so much Smile Smile

Continuation doubt, sorry for this

Regardless whatever(other than st|th|rd|nd) after the number it will print as it is.
For example, Now i am trying to add some special characters
with data as (select '42st/ation street, 42nd- East, W 23rd, New York' address from dual)
select regexp_replace(address, '(\d)(th|rd|nd|st)(\W|$)', '\1\3') new_addre from data

output as
42/ation street, 42- East, W 23, New York

expecting output is
42st/ation street, 42nd- East, W 23, New York

[Updated on: Thu, 29 August 2019 14:54]

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677199 is a reply to message #677198] Thu, 29 August 2019 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Change \W which means a non word character with all the characters you allow or not.
For instance, if this is a non word character which is not / then you can use [^\w/]:
SQL> with data as (select '42st/ation street, 42nd- East, W 23rd, New York' address from dual)
  2  select regexp_replace(address, '(\d)(th|rd|nd|st)([^\w/]|$)', '\1\3') new_addre from data
  3  /
NEW_ADDRE
-------------------------------------------
42st/ation street, 42- East, W 23, New York
Re: How to remove ordinal suffixes after the numbers [message #677200 is a reply to message #677199] Thu, 29 August 2019 15:24 Go to previous messageGo to next message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
Thank you so much Michel.
Re: How to remove ordinal suffixes after the numbers [message #677201 is a reply to message #677200] Thu, 29 August 2019 16:58 Go to previous messageGo to next message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
Hi Michel,

I have one more issue, consider this the situation a space followed(with the ordinal character , and .) will treat as ordinal character.

For Example
with data as (select '42st. /ation street, 42nd- East, W 23rd, New York' address from dual)
select regexp_replace(address, '(\d)(th|rd|nd|st)([^\w/]|$)', '\1\3') new_addre from data

NEW_ADDRE
-------------------------------------------
42st. /ation street, 42- East, W 23, New York

But my requirement is the expected output should be

NEW_ADDRE
-------------------------------------------
42 /ation street, 42- East, W 23 New York
Re: How to remove ordinal suffixes after the numbers [message #677210 is a reply to message #677201] Fri, 30 August 2019 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL> with data as (select '42st. /ation street, 42nd- East, W 23rd, New York' address from dual)
  2  select regexp_replace(address, '(\d *)(th|rd|nd|st)\.?([^\w/]|$)', '\1\3') new_addre from data
  3  /
NEW_ADDRE
------------------------------------------
42 /ation street, 42- East, W 23, New York
Re: How to remove ordinal suffixes after the numbers [message #677212 is a reply to message #677210] Fri, 30 August 2019 13:47 Go to previous message
nareshk00
Messages: 6
Registered: August 2019
Junior Member
Hi Michel,
Thanks for your response

I am not clear for the solution. Suggested me is it correct or not?

I gave a blank space if the ordinal characters followed by (. | , | blank) simply i will gave blank space. For Example,

Input:
42st./ation street, 42nd- East, W 23rd, New York

expected output is:
42 /ation street, 42nd- East, W 23 New York

I want to give a blank space when the ordinal characters comes with th.|th,|thblank)
Previous Topic: Purging Data From table!
Next Topic: ORA-04068: existing state of packages has been discarded
Goto Forum:
  


Current Time: Sun Oct 20 16:23:59 CDT 2019