OraFAQ Forum: SQL & PL/SQL » How to remove ordinal suffixes after the numbers

Home » SQL & PL/SQL » SQL & PL/SQL » How to remove ordinal suffixes after the numbers (pl/sql developer, windows 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to remove ordinal suffixes after the numbers [message #677186]

Thu, 29 August 2019 09:03

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.

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677187 is a reply to message #677186]

Thu, 29 August 2019 09:09

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677191 is a reply to message #677186]

Thu, 29 August 2019 10:46

Michel Cadot
Messages: 68417
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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677194 is a reply to message #677191]

Thu, 29 August 2019 11:49

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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677195 is a reply to message #677194]

Thu, 29 August 2019 13:28

Michel Cadot
Messages: 68417
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

nareshk00
Messages: 6
Registered: August 2019

Junior Member

Thank you so much 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

Michel Cadot
Messages: 68417
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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677200 is a reply to message #677199]

Thu, 29 August 2019 15:24

nareshk00
Messages: 6
Registered: August 2019

Junior Member

Thank you so much Michel.

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677201 is a reply to message #677200]

Thu, 29 August 2019 16:58

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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677210 is a reply to message #677201]

Fri, 30 August 2019 11:27

Michel Cadot
Messages: 68417
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

Report message to a moderator

Re: How to remove ordinal suffixes after the numbers [message #677212 is a reply to message #677210]

Fri, 30 August 2019 13:47

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)

Report message to a moderator

Previous Topic:	Purging Data From table!
Next Topic:	ORA-29913 ORA-29400 KUP-00554

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 22 08:42:37 CDT 2023