ignor coping date data

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
NiceMan331Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
PortletPaulfreelancerCommented:
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
NiceMan331Author Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
PortletPaulfreelancerCommented:
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
slightwv (䄆 Netminder) Commented:
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;

Open in new window

0
NiceMan331Author Commented:
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
NiceMan331Author Commented:
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
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
>> 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
NiceMan331Author Commented:
Shall I type another examples now ?
0
slightwv (䄆 Netminder) Commented:
>>Shall I type another examples now ?

Not right now.

Give me a few minutes.
0
NiceMan331Author Commented:
Ok , waiting for you
0
slightwv (䄆 Netminder) Commented:
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
PortletPaulfreelancerCommented:
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

Open in new window

0
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
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;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
>>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
NiceMan331Author Commented:
i hope to  learn every day from you & your team
0
NiceMan331Author Commented:
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
slightwv (䄆 Netminder) Commented:
>>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;
/

Open in new window

0
NiceMan331Author Commented:
THANX
i will test it today
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.