Extract substring equal to a 7 digits number (merged) [message #681122] |
Wed, 17 June 2020 21:26 |
annacol
Messages: 9 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 |
annacol
Messages: 9 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 #681135 is a reply to message #681134] |
Fri, 19 June 2020 00:06 |
|
Michel Cadot
Messages: 68665 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 |
annacol
Messages: 9 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 |
|
Michel Cadot
Messages: 68665 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 |
|
Michel Cadot
Messages: 68665 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 #681258 is a reply to message #681257] |
Tue, 30 June 2020 17:40 |
annacol
Messages: 9 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
|
|
|