Solved

# ignor coping date data

Posted on 2013-09-25
Medium Priority
468 Views
Last Modified: 2013-09-30
hi to all
i have one field , it getting its data from another field

the fields could having this data :

pay amount to AAA
receive bank transfer
pay rent on 01-sep-2013
pay rent 1300 SAR PER MONTH
pay note with bill 0111231
pay rent on 01/01/

what i need exactly is to transfer all text excluding any letter coming after the last char in the sentence
i mean to exclude any numbers or date or "/"  " - "
coming after the last char  , not in the middle

so the result should be like this

pay amount to AAA
receive bank transfer
pay rent on
pay rent 1300 SAR PER MONTH
pay note with bill
pay rent on

note , the 4th statement , we keep the number because it comes in between the char
0
Question by:NiceMan331
• 12
• 8
• 3
• +1
26 Comments

LVL 143

Expert Comment

ID: 39523904
>what i need exactly is to transfer all text excluding any letter coming after the last char in the sentence

this is very unclear in regards to the data samples you gave.
as human, I understand what you want, but take this one:
pay rent 1300 SAR PER MONTH

you don't want to remove the 1300 (decimals), which a "simple" remove anything starting from non-alpha character.

please clarify
0

LVL 49

Expert Comment

ID: 39523944
and, is it
a. one column, of one record?
b. one column, of six records?

ID TextColumn
1   pay amount to AAA receive bank transfer pay rent on 01-sep-2013 pay rent 1300 SAR PER MONTH pay note with bill 0111231 pay rent on 01/01/

ID Text Column
1    pay amount to AAA
2    receive bank transfer
3    pay rent on 01-sep-2013
4    pay rent 1300 SAR PER MONTH
5    pay note with bill 0111231
6    pay rent on 01/01/
0

Author Comment

ID: 39524154
yesr portpaul
one coulmn for one record
angel
sorry
another explaine
i want the code to remove any letter after the last char in the text

1- pay me 1000 USD  ,
2- pay me 1000 USD AFTER 01-sep-13   ,
3- pay me on 01-sep-13 , amount of 1000 ,
4-pay me 1000 USD /

result :
1- last char is D , keep it all :  pay me 1000 USD
2-last char is R , remove all after = pay me 1000 USD AFTER
3-last char is F , remove all after pay me on 01-sep-13 , amount of
4-last char is D , remove "/" , = pay me 1000 USD

HOPE IT IS CLEAR
0

LVL 143

Expert Comment

ID: 39524164
it's not really clear, because "last char" may be "clear" to you (your business need), but not clear in term of logic that you can put into SQL/PL-SQL/or something else.

hmmmm
0

LVL 49

Expert Comment

ID: 39524184
Is the origin of this information a file?
and then you load that file into a table?
----------------------------------------------------------
idea:

Why not simply UPDATE that column to A CONSTANT?

where that CONSTANT is equal to:

pay amount to AAA (hidden line feed)
receive bank transfer (hidden line feed)
pay rent on (hidden line feed)
pay rent 1300 SAR PER MONTH (hidden line feed)
pay note with bill (hidden line feed)
pay rent on
0

LVL 78

Expert Comment

ID: 39524456
I'm going to guess a lot here on your exact requirements based on the data and expected results you posted.

Looks like you want everything before the last set of characters after the last space that starts with a number.

See if this works for you.  If produces your expected results.

If it doesn't work, please provide more sample data and why it no longer works.

``````drop table tab1 purge;
create table tab1(col1 varchar2(100));

insert into tab1 values('pay amount to AAA');
insert into tab1 values('receive bank transfer');
insert into tab1 values('pay rent on 01-sep-2013');
insert into tab1 values('pay rent 1300 SAR PER MONTH');
insert into tab1 values('pay note with bill 0111231');
insert into tab1 values('pay rent on 01/01/');
commit;

select regexp_replace(col1,' [0-9].*\$',null,instr(col1,' ',-1))
from tab1;
``````
0

Author Comment

ID: 39524946
sligtow
may be you got it
but i dont know what this function will do
select regexp_replace(col1,' [0-9].*\$',null,instr(col1,' ',-1))
from tab1;
i will try it in my office
but any how , it is not necessay to be a space after the required char
angel
what i mean for char is [A-Z]
last char for me is last [a-z] IN THE TEXT
then remove every thing after it
0

LVL 143

Expert Comment

ID: 39525008
see:
2- pay me 1000 USD AFTER 01-sep-13   ,
result :
2-last char is R , remove all after = pay me 1000 USD AFTER

If your logic is "remove everything after the last upper case letter, this would explain above.
but then:

pay note with bill 0111231
=>
pay note with bill

but "l" is not a upper case, so why remove "after" ?

if the logic is "remove all after the last character+space", THEN it starts to make sense for these, but then again:
pay amount to AAA
=> would go to pay amount to
and not stay with the AAA

See, your rules are not 100% clear.
maybe the suggestion with the regular expression will work fine, but ...
0

Author Comment

ID: 39526323
Angel.                                                                  May be I'm not 100% clear but at the end I'm not care about upper case or lower ,          I want to remove any thing after last character [betwee A to Z ]. ,                              This role will answer the 3 of your question above , yes , remove every thing after the last A->Z.
0

LVL 78

Expert Comment

ID: 39526329
>>yes , remove every thing after the last A->Z.

This implies case sensitivity.  That requirement doesn't match up with your expected results.

If my assumptions aren't correct, please provide more sample data and expected results.

We can probably understand the requirements better by seeing it.
0

Author Comment

ID: 39526342
More examples :                                                    1- he pay 1000 more than last September        2- he pay @ : /. HHHH ~ j right.                     3- he pay him3000.                                            4- he not pay him/.                                              5- he -----||||||| he )( &@ go@&.                               Results :                                                              1- don't remove any thing because last letter is within A-Z.                                                        2- also last letter is within A-Z.                          3- remove the 3000 cause m is the last letter between a and z.                                                4- remove /. Because last letter is m                  5- last letter is o. Of go , so remove every thing after it
0

Author Comment

ID: 39526365
Sorry if not clear again , I'm typing now from my cell phone , if still not clear , I'll wright again tomorrow from my laptop
0

LVL 78

Expert Comment

ID: 39526369
>> I'm typing now from my cell phone , if still not clear

I got it even though it didn't line up.

Looking at it now.
0

Author Comment

ID: 39526376
Shall I type another examples now ?
0

LVL 78

Expert Comment

ID: 39526380
>>Shall I type another examples now ?

Not right now.

Give me a few minutes.
0

Author Comment

ID: 39526383
Ok , waiting for you
0

LVL 78

Expert Comment

ID: 39526397
OK,
Here is the problem with your requirements:  last character by itself isn't good enough.

Based on that requirement you have conflicting data and expected results:

Provided data:
he pay him3000.
pay me 1000 USD AFTER 01-sep-13

Expected results:
remove the 3000 cause m is the last letter between a and z.
pay rent on

Based on your latest requirement "pay me 1000 USD AFTER 01-sep-13" should produce:
"pay me 1000 USD AFTER 01-sep"
0

LVL 49

Expert Comment

ID: 39526468
could we "step back" from the nitty gritty detail of the strings for a moment?

why are you wanting this?
what do you expect to do with these modified strings after they are modified?

could you "reverse your thinking"?
i.e. hold the items of data (as seperate fields) then generate the wanted string?

Is this for a report?

purely an example:
``````**Oracle 11g R2 Schema Setup**:

CREATE TABLE TAB2
(VAL1 varchar2(20), VAL2 varchar2(20), VAL3 timestamp, VAL4 varchar2(20), VAL5 int, VAL6 varchar(20))
;

INSERT ALL
INTO TAB2 (VAL1, VAL2, VAL3, VAL4, VAL5, VAL6)
VALUES ('AAA', 'bank transfer', '01-Sep-2013 12:00:00 AM', '1300 SAR PER MONTH', 0111231, '01/01')
SELECT * FROM dual
;

**Query 1**:

select
'pay amount to '  || val1
|| ' receive '       || val2
|| ' pay rent on '   || to_char(val3,'YYYY-MM-DD')
|| ' pay rent '      || val4
|| ' pay note '      || val5
|| ' pay rent on '   || val6
as the_string
from tab2

**[Results][2]**:

|                                                                                                                   THE_STRING |
|------------------------------------------------------------------------------------------------------------------------------|
| pay amount to AAA receive bank transfer pay rent on 2013-09-01 pay rent 1300 SAR PER MONTH pay note 111231 pay rent on 01/01 |

[1]: http://sqlfiddle.com/#!4/af6ee/5

[2]: http://sqlfiddle.com/#!4/af6ee/5/0
``````
0

Author Comment

ID: 39526654
Slightwm.   You are correct now to my requirement , if you do it it will be excellent even with this result :                                              Based on your latest requirement "pay me 1000 USD AFTER 01-sep-13" should produce:
"pay me 1000 USD AFTER 01-sep".                  Totally correct , it will be ok for me
0

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39528056
See if this works for you.

If not, please add to the test case and explain why if fails.

``````drop table tab1 purge;
create table tab1(col1 varchar2(100));

insert into tab1 values('pay amount to AAA');
insert into tab1 values('receive bank transfer');
insert into tab1 values('pay rent on 01-sep-2013');
insert into tab1 values('pay rent 1300 SAR PER MONTH');
insert into tab1 values('pay note with bill 0111231');
insert into tab1 values('pay rent on 01/01/');
insert into tab1 values('pay me 1000 USD,');
insert into tab1 values('pay me 1000 USD AFTER 01-sep-13, ');
insert into tab1 values('pay me on 01-sep-13 , amount of 1000 , ');
insert into tab1 values('pay me 1000 USD / ');
insert into tab1 values('he pay 1000 more than last September');
insert into tab1 values('he pay @ : /. HHHH ~ j right.');
insert into tab1 values('he pay him3000.');
insert into tab1 values('he not pay him/.
insert into tab1 values('he -----||||||| he )( &@ go@&.');

select substr(col1,1,regexp_instr(col1,'[^a-zA-Z]*\$')-1)
from tab1;
``````
0

Author Comment

ID: 39528318
excellent
that what i exactly need
you done
i got my answer , i have to accept now
can you please do  one favore for me for the future requirement
if later on i wish to remove any thing else of [A-Z] , in any position within the text
not only the last one
for example
'pay me 1000 USD AFTER 01-sep-13, '
it should return :
'pay me  USD AFTER sep '
is it possible ?
0

LVL 78

Expert Comment

ID: 39528351
>>is it possible ?

Anything is possible.  Removing everything except letters is even easier:

regexp_replace(col1,'[^ A-Za-z]')

For example:
select regexp_replace('pay me 1000 USD AFTER 01-sep-13, ','[^ A-Za-z]') from dual;

Regular expressions are really powerful but do take some time to learn.
0

Author Comment

ID: 39528514
i hope to  learn every day from you & your team
0

Author Comment

ID: 39532191
sorry slightlaw
is that function not applied for form 6i ?
when i test your code , i tested in database 10 g
but when applied it to form 6 i  , it not accept it
0

LVL 78

Expert Comment

ID: 39532781
>>is that function not applied for form 6i ?

6i is very old and I doubt it understands the regexp functions.  This was also the first time you mentioned Forms.  You might want to provide your full requirements in the initial question.

I would suggest you create your own function in the database and call that from 6i.

Something like (untested, just typed in):

``````create or replace function myStringClean(p_input in varchar2) return varchar2
is
v_result varchar2(4000);
begin
select substr(col1,1,regexp_instr(p_input,'[^a-zA-Z]*\$')-1) into v_result from dual;
-- this might also work without the select but I didn't test it
-- v_result := substr(col1,1,regexp_instr(p_input,'[^a-zA-Z]*\$')-1);
return v_result;
end;
/
``````
0

Author Comment

ID: 39535522
THANX
i will test it today
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Join & Write a Comment Already a member? Login.

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month10 days, 5 hours left to enroll

#### 619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.