SQL & PL/SQL :: Replacing Vowels In A String?
Apr 13, 2013
I am looking to replace the vowels in a string, and get rid of the empty spaces afterwards.
Our Lab Book suggested that we use the REPLACE function, but I have only been able to get it to work using the TRANSLATE function.
Also, I can`t figure out how to get the empty spaces out of the string when output, I tried to use the TRIM command unsuccessfully.
1) Is it possible to use REPLACE instead of TRANSLATE to replace the vowels?
2) How would i get rid of the empty spaces when the vowels have been replaced?
CREATE OR REPLACE PROCEDURE replacingvowels (vowels IN VARCHAR2, var IN VARCHAR2)
IS
vowelreplace VARCHAR2(50);
BEGIN
[code]....
View 5 Replies
ADVERTISEMENT
Jun 22, 2010
I am facing a simple problem, but could not resolve as yet, i want to replace two string 'M/S' and ' " ' with null, i know this command SELECT REPLACE(' " M/S Private linker " ','M/S',NULL) FROM dual Which command i should use
View 3 Replies
View Related
Jun 17, 2010
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
View 7 Replies
View Related
Dec 14, 2010
I am reading Section 4-8 (page 42/216) in the Oracle Database 2-day Developer Guide from here:
[Code]....
It reads:
Suppose that you want to select every employee whose last name has a double vowel(two adjacent occurrences of the same vowel).
Example 4.9 shows how you can do this.
The regular expression ([AEIOU]) represents any vowel. The metacharacter 1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.
Example 4.9 Selecting All Employees Whose Last Names Have Double Vowels
SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])1', 'i');
Result is similar to:
FIRST_NAME LAST_NAME
-------------------- -------------------------
Harrison Bloom
Lex De Haan
Kevin Feeney
Ki Gee
Nancy Greenberg
Danielle Greene
Alexander Khoo
David Lee
8 rows selected.
I don't understand how [AEIOU])1 would find a double vowel(two adjacent occurrences of the same vowel).
View 6 Replies
View Related
Jul 26, 2011
I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:
following is my trial:
create table abc
(
pk_id number(4) primary key,
abc_name varchar2(10),
fk_id number(4)
);
create table xyz
(
x_id1 number(4),
x_id2 number(4),
x_name varchar2(10)
);
commit;
insert all
into abc values (10,'ab1',1)
into abc values (11,'ab2',2)
into abc values (12,'ab3',5)
into abc values (13,'ab4',7)
into abc values (14,'ab5',9)
into xyz values (1,1,'d1')
into xyz values (2,2,'d2')
into xyz values (5,3,'d3')
into xyz values (7,4,'d4')
into xyz values (9,5,'d5')
select * from dual;
commit;
--this following select returns 5 rows
select * from abc, xyz where abc.fk_id = xyz.x_id1;
-- the following update only updates 3 rows and sets the other 2
-- to null!
update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id);
commit;
select * from abc;
View 3 Replies
View Related
Apr 23, 2010
Is there a way to replace a field with another if the particular value returned is null?
View 11 Replies
View Related
Jul 25, 2012
In forms 11 g, whenever, We did any menu change, and deploy in server, same changes are not reflecting in clients machine immediatly. I even stop forms services and restart.After some time... generally after 1 day, it reflect the new changes.In forms 10G, I never face this issue. As soon as i close the old menu and re-open, new changes reflect.
View 2 Replies
View Related
May 21, 2013
I am doing some ETL that I need to run "faster". The function in which I am interested removes low ascii code characters from a string. Please see the timing below and the definitions of the of the functions below those. I am selecting just the first 100K rows for testing and timing purposes only. In production, we are doing millions of records several times a day, thus the desire for "faster". Selecting with no functions is very fast, 0.2 seconds. We would really really want to convert at least 100K rows per second.
The best I can do is get it down to around five seconds using clear_nonlegal. That is, ironically, the one that I thought would be the slowest. It's making thirty-one calls to REPLACE. I would have guessed that the other two would be much faster. I am guessing that REPLACE is just much better optimized than TRANSLATE and, of course, my homegrown PL/SQL, which isn't optimized at all.
So, my question is thisif there is a way I can optimize my custom function, or maybe know of a better already optimized standard SQL and/or Oracle function that would do the job? I am thinking about trying to use a Java stored procedure, but I have never done that before, I am not currently set up for it, if it would be any faster anyway. Is Java faster with string manipulation the PL/SQL? I am thinking it would be really fast to call a C method,
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL> set timing on
SQL> SELECT COUNT(*)
2 FROM (SELECT DISTINCT keyword_dest_url
3 FROM se_keywords sek
[code]...
View 18 Replies
View Related
Nov 15, 2012
I did an expdp on the prod DB and have been doing a straight impdp as a test, just to have data to work with, but it spews 214 errors. Mostly these:
ORA-31684 lots
ORA-39083 some
ORA-39151 lots
ORA-39082 lots
ORA-39111 lots
ORA-39112 lots
I can see that I can use REUSE_DATAFILES & TABLE_EXISTS_ACTION to overwrite tables by default, but is there a recognised way of replacing the entire DB with the impdp? Do I just create the instance, (with the init file) and not build/init the tables, or what? I'll experiment, but I'm just interested if there is a DBA best practice for this sort of thing.
View 3 Replies
View Related
May 16, 2013
why the REPLACE function is not replacing. I assume it has something to do with the ASCII value being zero.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Connected as aggs@AGGSTEST
SQL>
SQL> SELECT str,
2 e9,
3 REPLACE(str, '%E9', e9) replace,
4 regexp_replace(str, '%E9', e9) regexp_replace,
5 utl_url.unescape(str, 'UTF8') utl_url,
6 ascii(e9) ascii
7 FROM (SELECT 'Soir%E9e' str,
8 chr(to_number('E9', 'xx')) e9
9 FROM dual);
STR E9 REPLACE REGEXP_REPLACE UTL_URL ASCII
-------- --- ------- -------------- ------- -----
Soir%E9e é Soire Soirée Soirée 0
View 5 Replies
View Related
Jan 11, 2011
Need to create or replace a Standard Sunguard package. When I'm executing, It is asking some series of parameters. when I cancel It is executing but the Web page is not opening giving error like PL/SQL: could not find program unit being called . Not understanding the exact error.
View 5 Replies
View Related
Dec 15, 2010
The following query calls the function get_meter_desc 8 times.
SELECT iu.instd_unit_id, iu.fk_cust_id, bill_cd,
iu.mfg_prod_cd, iu.mfg_prod_seq_no, ccequip.fk_mkt_cd,
eff_tmstmp, cust_ord_id, ord_dt, iu.xnac_co_cd,
iu.xnac_div_cd, smmr_wvr_cd, warr_expn_dt,
iu.auto_replen_ind, iu.ms_stat_cd,
inst_dist_id, instn_dt, iu.last_auto_dt,
iu.replen_freq_vlu, cpc_pln_cd, std_sply_ind,
emcv_total_qty, tot_actl_cpy_qty, init_emcv_tot_qty, ms_tran_cd,
[code]...
How can I replace the function call by the join in the main query?
View 19 Replies
View Related
Jan 31, 2013
Getting starting with diving deeper into APEX and reading the developers guide all 800 pages of it! Thing is I'd like to get started with some of the more interesting stuff.
I have created a form page based on the "Form on a Table with Report". The report part looks fine. What I'd like to do now is use an API I've built to change how the insert and update works. So when the submit button is pressed it rather performs an API call to the procedure/function.
1. Should the API be a function or procedure. In my playing around I saw that a procedure might work better or easier to implement
2. How would I go about creating a call for the button to use instead of the current processes?
----------------------------
Apex 4.2
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Linux version 2.6.32-279.19.1.el6.i686
----------------------------
View 4 Replies
View Related
Dec 3, 2010
I have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
View 3 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Mar 14, 2013
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
View 3 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
May 10, 2010
I have a string like below:
string = 'HEADER||MEAL||15'
How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?
View 9 Replies
View Related
May 22, 2009
I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.
View 1 Replies
View Related
Oct 12, 2007
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
View 6 Replies
View Related
Oct 9, 2008
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
View 2 Replies
View Related
Aug 22, 2013
how to get first word from string.example i have string like:-
Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some
i am expecting first word as out put like :-
jack
mark
Raj
Deniel
Some
in the same maner if i want should get two words also.
View 36 Replies
View Related
Dec 12, 2010
how to get last 4 characters in a string. But i don't know the length , for example the string is
abcdefghij
i want only ghij.
View 5 Replies
View Related
Aug 30, 2010
I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).
I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.
Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )
View 8 Replies
View Related
Apr 6, 2012
i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like
sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>
in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.
where <Insurer> and <I-Care ID> records are in two different tables
where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text
View 3 Replies
View Related
Dec 21, 2011
I have strings like
1) ICE_10001 ICE_10002 ICE_10003
2) ICE_10005 ICE_10006
i want to split above strings like
1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006
If it is possible in oracle sql
View 15 Replies
View Related
Oct 31, 2013
I want to convert the follow string to date: 2013-12-04 11:35:54.89
View 7 Replies
View Related
Jul 12, 2010
I have the following test case:
create table t(
col1 varchar2(10),
col2 varchar2(100))
[Code]...
I need to spit col2 like this
P0001 aaa
P0001 ddsd
P0001 sds
.
.
.
.
.
delimeter between individual may be #, ' ',',' or';'
How can I achieve this using PL/SQL
View 13 Replies
View Related
Jun 26, 2012
I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.
Eg.
Input String :
Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .
Output string :
Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown
View 6 Replies
View Related
Feb 7, 2007
if it is possible to randomize a string
for example if i have string value of ('ABC') how could i get Oracle to randomize the output say to 'BCA' for example
View 1 Replies
View Related
