Home » SQL & PL/SQL » SQL & PL/SQL » Extract substring equal to a 7 digits number (merged)
Extract substring equal to a 7 digits number (merged) [message #681122] Wed, 17 June 2020 21:26 Go to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi

I would like to extract 7 digits number from a string. If there are two 7 digit numbers within a string then I want the number that starts with 6.

Example - Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct.

In the above case return value should be 7234569.

create table test_rec (description varchar (100));

insert into test_rec (description) values ('Postage 8675432 paid on the 1st May 2020 ref A23456 to acct 363');

insert into test_rec (description) values ('Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct');

Thanks
Regards
Anna
Extract substring equal to a 7 digits number [message #681123 is a reply to message #681122] Wed, 17 June 2020 21:26 Go to previous messageGo to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi

I would like to extract 7 digits number from a string. If there are two 7 digit numbers within a string then I want the number that starts with 6.

Example - Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct.

In the above case return value should be 7234569.

create table test_rec (description varchar (100));

insert into test_rec (description) values ('Postage 8675432 paid on the 1st May 2020 ref A23456 to acct 363');

insert into test_rec (description) values ('Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct');

Thanks
Regards
Anna
Re: Extract substring equal to a 7 digits number (merged) [message #681124 is a reply to message #681122] Thu, 18 June 2020 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want the number that starts with 6.
Quote:
In the above case return value should be 7234569.

It does not start with 6 so why this result?

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

Re: Extract substring equal to a 7 digits number (merged) [message #681125 is a reply to message #681124] Thu, 18 June 2020 00:42 Go to previous messageGo to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
If there are two 7 digit numbers within a string then I want the number that starts with 6
Re: Extract substring equal to a 7 digits number (merged) [message #681126 is a reply to message #681125] Thu, 18 June 2020 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So in your test case, you return nothing?

Re: Extract substring equal to a 7 digits number (merged) [message #681127 is a reply to message #681125] Thu, 18 June 2020 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

annacol wrote on Thu, 18 June 2020 07:42
If there are two 7 digit numbers within a string then I want the number that starts with 6
Quote:
In the above case return value should be 7234569.

Once again 7234569 does not start with 6!
Re: Extract substring equal to a 7 digits number (merged) [message #681134 is a reply to message #681127] Thu, 18 June 2020 17:11 Go to previous messageGo to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi Michel Cadot

Sorry for the typo in the original posting. My requirement is

If there are two numbers within a string, then I want the number that starts with 7

I am expecting the select statement to return the following results

7234569
8675432

Thanks
Anna
Re: Extract substring equal to a 7 digits number (merged) [message #681135 is a reply to message #681134] Fri, 19 June 2020 00:06 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
If there are two numbers within a string, then I want the number that starts with 7
So now the length of the number does not matter?

If there are 2 numbers but none starts with 7, what should be the result?
If there are 2 or more numbers staring with 7, which one to return?
Please post a COMPLETE test case, one that covers ALL the cases, and give the result for it.

[Updated on: Fri, 19 June 2020 00:12]

Report message to a moderator

Re: Extract substring equal to a 7 digits number (merged) [message #681158 is a reply to message #681135] Sun, 21 June 2020 17:55 Go to previous messageGo to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi Michel,

Please find below the test cases and my requirements. The requirement is I need the number that contains ONLY 7 digits.

create table test_rec (cust_no varchar(3), description varchar (100));

insert into test_rec (cust_no, description)
values ('100', 'Postage 8675432 paid on the 1st May 2020 ref 23456 to acct number 363');

insert into test_rec (cust_no, description)
values ('200', 'Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct');

insert into test_rec (cust_no, description)
values ('300', 'Cab charges paid to TYZ Ltd');

insert into test_rec (cust_no, description)
values ('400', 'Uber charges 7234567 to XYZ Ltd 5463211');

insert into test_rec (cust_no, description)
values ('500', '34567 Courier charges paid to ABC Co. Ref - 46321');

insert into test_rec (cust_no, description)
values ('600', 'Postage paid Ref xyz1234567');

commit;

Expected Results (along with the requirements)

cust_no Remit_No (from the description column)

100 8675432 (criteria 1 - Number that contains 7 digits)
200 7234569 (criteria 2 - Two numbers contain 7 digits - results should be the number that starts with '7')
300 7234567 (criteria 3 - Two (or more) numbers contain 7 digits and also starting with digit '7'- results should be the first occurence)
400 0 (as there are no numbers)
500 0 - there is no number that contains 7 digits
600 0 - as the number contains alpha numeric characters though it contains 7 digits)

Hope this helps

Thanks
Regards
Anna
Re: Extract substring equal to a 7 digits number (merged) [message #681159 is a reply to message #681158] Mon, 22 June 2020 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Two cases are missing in the test case:
  • there are two (or more) 7 digit numbers but none start with 7
  • there are two (or more) 7 digit numbers and all start with 7
In both cases I assume "results should be the first occurence"
SQL> select cust_no, description,
  2         case
  3           when    regexp_count(description, '(^| )\d{7}( |$)') > 0
  4               and regexp_count(description, '(^| )7\d{6}( |$)') > 0
  5             then trim(regexp_substr(description, '(^| )7\d{6}( |$)'))
  6           when regexp_count(description, '(^| )\d{7}( |$)') > 0
  7             then trim(regexp_substr(description, '(^| )\d{7}( |$)'))
  8           else to_char('0')
  9         end res
 10  from test_rec
 11  order by cust_no
 12  /
CUS DESCRIPTION                                                             RES
--- ----------------------------------------------------------------------- ----------
100 Postage 8675432 paid on the 1st May 2020 ref 23456 to acct number 363   8675432
200 Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct 7234569
300 Cab charges paid to TYZ Ltd                                             0
400 Uber charges 7234567 to XYZ Ltd 5463211                                 7234567
500 34567 Courier charges paid to ABC Co. Ref - 46321                       0
600 Postage paid Ref xyz1234567                                             0
700 something 1234567 other thing 0654321                                   1234567
800 7654321 another valid 7 digits 7890321                                  7654321

[Updated on: Mon, 22 June 2020 01:35]

Report message to a moderator

Re: Extract substring equal to a 7 digits number (merged) [message #681160 is a reply to message #681159] Mon, 22 June 2020 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or, if you want a result as a number instead of a string:
SQL> select cust_no, description,
  2         case
  3           when    regexp_count(description, '(^| )\d{7}( |$)') > 0
  4               and regexp_count(description, '(^| )7\d{6}( |$)') > 0
  5             then to_number(regexp_substr(description, '(^| )7\d{6}( |$)'))
  6           when regexp_count(description, '(^| )\d{7}( |$)') > 0
  7             then to_number(regexp_substr(description, '(^| )\d{7}( |$)'))
  8           else 0
  9         end res
 10  from test_rec
 11  order by cust_no
 12  /
CUS DESCRIPTION                                                                    RES
--- ----------------------------------------------------------------------- ----------
100 Postage 8675432 paid on the 1st May 2020 ref 23456 to acct number 363      8675432
200 Internet charges 1234567 and paid on the 11th May - ref 7234569 to acct    7234569
300 Cab charges paid to TYZ Ltd                                                      0
400 Uber charges 7234567 to XYZ Ltd 5463211                                    7234567
500 34567 Courier charges paid to ABC Co. Ref - 46321                                0
600 Postage paid Ref xyz1234567                                                      0
700 something 1234567 other thing 0654321                                      1234567
800 7654321 another valid 7 digits 7890321                                     7654321
Re: Extract substring equal to a 7 digits number (merged) [message #681257 is a reply to message #681122] Tue, 30 June 2020 17:36 Go to previous messageGo to next message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi Michael

Thanks for the query. I need only the numbers that starts with '7'. In the above case, CUS 100 and 700 should have zero as results.

Regards
Anna
Re: Extract substring equal to a 7 digits number (merged) [message #681258 is a reply to message #681257] Tue, 30 June 2020 17:40 Go to previous message
annacol
Messages: 8
Registered: April 2006
Location: Sri Lanka
Junior Member
Hi Michael

I just modified the query as follows and it worked.

select cust_no, description,
2 case
3 when regexp_count(description, '(^| )\d{7}( |$)') > 0
4 and regexp_count(description, '(^| )7\d{6}( |$)') > 0
5 then to_number(regexp_substr(description, '(^| )7\d{6}( |$)'))
6 when regexp_count(description, '(^| )\d{7}( |$)') > 0
7 then to_number(regexp_substr(description, '(^| )\d{7}( |$)'))
8 else 0
9 end res
10 from test_rec
11 order by cust_no

Thanks again for your help.

Regards
Anna
Previous Topic: PLS-00801: internal error [*** ASSERT at file pdz2.c, line 4788; Illegal access (Text)
Next Topic: Help with the minus operator
Goto Forum:
  


Current Time: Mon Sep 28 11:01:12 CDT 2020