SQL & PL/SQL :: Finding Double Vowels
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
ADVERTISEMENT
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
View Related
Oct 12, 2011
I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
View 6 Replies
View Related
Aug 20, 2012
i'm trying to read a text file into an oracel table. I'm having issues with this statement FIELDS TERMINATED BY ' ' fields are separated by 2 spaces. How ever Oracle is not recognizing ' ' or " " as double space. It is treating as one space thus shifting all coulmns.
How can I represent double space to Oracle ?
View 12 Replies
View Related
Nov 10, 2010
i have table contains a column of var char type i want to insert a value
'1 mmTHICK GI SHEET 4' X 8' X 1MM THICH' in to the coulmn but m getting error
I tried set scan off but its not worrking for the below query.
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
my query is
Insert into Inventory select
'N1280000015',
'1 mmTHICK GI SHEET 4''' X 8''' X 1MM THICH',
[code]....
View 8 Replies
View Related
Dec 4, 2011
declare
v_a varchar2(2000) := 'abcd';
v_e varchar2(2000) := '6666';
v_d varchar2(2000) := 'example';
v_final varchar2(4000);
begin
v_final := '"v_a"'||'''|'''||'"v_e"'||'''|'''||'"v_d"';
-- v_final := '"v_a"';
dbms_output.put_line('v_final: '||v_final);
end;
/
above gives me :
v_final: "v_a"'|'"v_e"'|'"v_d"
so it is printing the variable names, But I want to see values, like this: "abcd"|"6666"|"example"
View 6 Replies
View Related
Dec 6, 2010
I need to write a procedure to be able to reuse it to clean up subcategory sequence. Here is a problem: two tables: Category and subcategory:
create table Category
(category_id number,
Name varchar2(20))
/
create table Sub_Category
(sub_category_id number,
category_id number,
sequence number)
/
In the ideal world each subcategory of a single category would have unique sequence so if there are 3 subcategory for the same category then each of them would have 1,2,3 in sequence, if there are 5 subcategories then 1,2,3,4,5 for each of them etc.
Problem I'm facing is that some of the subcategories sequences for the same category has the same values . For instance for 4 subcategories of the same category, each of them has 1 (1,1,1,1) in a sequence.
So ideal world is :
Insert into Category values (123 ,'Category1');
Insert into Category values (234 ,'Category2');
Insert into Category values (345 ,'Category3');
Insert into Category values (456 ,'Category4');
Insert into Category values (567 ,'Category5');
Insert into Sub_Category values (1,123,1);
Insert into Sub_Category values (2,123,2);
Insert into Sub_Category values (3,123,3);
But I've also bad rows like this:
Insert into Sub_Category values (4,234,1);
Insert into Sub_Category values (5,234,1);
Insert into Sub_Category values (6,234,1);
Insert into Sub_Category values (7,345,1);
Insert into Sub_Category values (8,345,1);
[code].....
Fix for this and my goal is to select all such cases where subcases have mixed up sequences as above and give them randomly numbers starting from 1. So if there are 3 subcategories like for CATEGORY 2 then just apply random number to the sequence of the subCATEGORIES like 1,2,3. For CATEGORY 3 : 1,2,3 to 7.
I was thinking to write two procedures one selecting all the categories and passing category ID to the other procedure that would actually update sequence, like this:
CREATE OR REPLACE PROCEDURE SCHEMA.SELECT_CATEGORY
IS
CURSOR c1
IS
select category_ID from category where ...;
BEGIN
FOR a IN c1 LOOP
UPDATE_SUBCATEGORY(a.Category_id);
COMMIT;
END LOOP;
END;
/
And the actual procedure updating subcategory:
CREATE OR REPLACE PROCEDURE SCHEMA.UPDATE_SUBCATEGORY
BEGIN
............
END;
/
write PROCEDURE SCHEMA.SELECT_CATEGORY cursor to not miss any of the categories ID having mixed up subcategory. There can be any of the doubled sequences like doubled 1 value (this is majority) but there can be any other doubles (or at least I need to make sure that there aren't any other doubles 2 values or 3 values in sequence etc.)
And how to write SCHEMA.UPDATE_SUBCATEGORY to loop through rows of subcategory and update sequence with values starting from 1 ?
View 17 Replies
View Related
Apr 29, 2010
I have oracle 10g database running on Sun Solaris. My database character set is AL32UTF8. Our user wants to store a double byte character and an image, I want to know what the column type should be using for these.
View 2 Replies
View Related
Jun 9, 2010
I support old oracle code and we currently print using the set report object property.
I recently was requested to modify all of our printing to two-sided printing from the current one page per sheet.
I am trying to figure out if there is a parameter I can set, i.e.REPORT_OTHER or something else that would allow me to do this.
View 4 Replies
View Related
Aug 22, 2013
I am loading .csv file into Oracle using sql loader file has strings, and numberics, Strings are surrounded by double quotes(") and field terminated by comma(,)
load data
BADFILE '/var/opt/app/bad/filename'
DISCARDFILE '/var/opt/app/discard/filename'
append into table source_file
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
Some time String fields may have double quotes in it, at that time it is rejecting the records. how to handle those records to load into table.
View 3 Replies
View Related
Oct 29, 2012
I want to remove more than one space from a string by excluding double quotes.
For example:
I/P: Item .getChildByType(" Agreement").getParent( ) .hasChildByType("Agreement ")
O/P : Item.getChildByType(" Agreement").getParent().hasChildByType("Agreement ")
View 17 Replies
View Related
Feb 14, 2012
Any way to replace the Double Quotes used to enclose column names with an alternative character. This is the SQL I have now that Works!
select (case when CUST is null then "/BIC/Z_SUPPLNT" else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where "/BIC/Z_MAJVEND"=CUST(+) and Material = '1ABCD456'
order by Material
But would like to do something along these lines below but keep getting error "ORA-00936: missing expression".
select (case when CUST is null then chr(34)||/BIC/Z_SUPPLNT||chr(34) else NM1 end) CMPNTSUPSRCE
from TBL1, TBL2
where chr(34)||/BIC/Z_MAJVEND||chr(34)=CUST(+) and Material = '1ABCD456'
order by Material
View -1 Replies
View Related
Aug 25, 2010
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line.
"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA"
Expected: the one below is exactly 60bytes.
PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT
LOAD DATA
INFILE *
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|"
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD",
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT,
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)",
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD",
ITM_CMNT "TRIM(:ITM_CMNT)")
View 4 Replies
View Related
Jul 1, 2013
I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
View 8 Replies
View Related
Jun 19, 2013
I have the following query where i need to find the max value[code] with t as
( select 'L1' R_nm ,'Data' R_Data , 'Obj' R_Obj , 'Wd' r_prec , '2' val
from dual union all select 'L1' , 'Data', 'Obj' , 'No', '4'
from dualunion all select 'L2' , 'Data', 'Obj' , 'No', '4' from dual )
select t.*, max(val) over(partition by r_nm,r_data,r_obj) rk from t[/code]
My expected output should be
[code]
r_nm r_data r_obj r_prec val rkL1 Data Obj Wd 2 1L1 Data Obj No 4 2L2 Data Obj Yes 1 1
[/code]
View 1 Replies
View Related
Dec 4, 2011
I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my
CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK
[Code] ............
There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:
ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found
I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..
View 2 Replies
View Related
Oct 31, 2012
Select * from one:
ID SALARY
----------------------
10 1000
20 2000
30 3200
Select * from two:
ID SALARY
----------------------
10 1000
20 2000
40 4000
10 3000
20 4000
30 3000
When i try to find ID,MAX(SALARY) from these two tables, i am getting this output:
Select id,max(salary)
from
(select * from one
union
select * from two)
group by id
order by max(salary) desc;
OUTPUT:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
30 3200
10 3000
I want OUTPUT to be like this:
ID MAX(SALARY)
--------------------------------------
20 4000
40 4000
View 3 Replies
View Related
Jun 23, 2008
What i have is table with:
mission_id, mission_type_id, security_level and code_name.
What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7
So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order
sql
UPDATE missions m
SET m.security_level = (
SELECT max(m2.security_level)
FROM missions m2
WHERE m2.mission_type_id = m.mission_type_id
AND length(m2.code_name) > 7
)
WHERE m.mission_ID IN (
Select m3.mission_id
From missions m3
ORDER BY m3.mission_id desc)
View 3 Replies
View Related
Dec 23, 2011
How can i come to know that which current alert log file is being used for database? Is their a command at database level to find out the current alert log file to which database is using ?
View 2 Replies
View Related
Oct 4, 2007
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies
View Related
Nov 3, 2008
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
View 8 Replies
View Related
May 30, 2011
How to find the list of tables which has permission select only and tables which has both read and write access permission for a particular user.
I'm using ORACLE database.
View 1 Replies
View Related
Nov 22, 2011
finding the difference between IN and EXIST.
View 15 Replies
View Related
Feb 7, 2012
I want to track the session id that has been initiated by a particular job (I have job ids with me). Which dictionary views I can get this result.
View 1 Replies
View Related
Nov 1, 2011
@test.sql
accept 1 num prompt 'enter begin value '
accept 2 num prompt 'enter end value '
Need to display all values between the above given inputs.
No tables involved.
Need SQL level solution in version 8i.
View 4 Replies
View Related
May 20, 2010
i have table with name, count, flag with dublicate records
example
with swayam name , counts are 3, 4
with ramana name, counts are 5,5
with reddy name, counts are 1,2,3
i want to update the flag
if count are same then update one of record (flag='A') and other should be flag='R'
if count are different then update the max count (flag='A') and other should be reject remaing (flag='R'). use below quires
CREATE TABLE TEST_DUB ( NAME VARCHAR2(99), V_COUNT NUMBER, FLAG VARCHAR2(1));
Insert into TEST_DUB (NAME, V_COUNT)
Values
('SWAYAM', 3);
Insert into TEST_DUB
(NAME, V_COUNT)
Values
[Code]....
View 4 Replies
View Related
Aug 1, 2012
I have a partitioned tables and local partitioned index on the same.
I want to know that a particular index partition belong to which table partition.
View 1 Replies
View Related
Jan 12, 2012
Version : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
I have a source which is of datatype RAW(1). Basic requirement is to convert it into binary, and then lookup the value of the 4th and 6th bit. Is this possible using simple SQL ?
Eg.
DataBinary
----- -------
EF1110 1111
From this binary output, i need to find the value of 4th and 5th bit.
So if the 4th bit is 1, I need to perform some operations if 0 then some other.
Create table test_hex_to_bin (col1 raw(1));
INSERT into test_hex_to_bin values ('99');
INSERT into test_hex_to_bin values ('EF');
INSERT into test_hex_to_bin values ('AB');
Basically the output should read as
Bit_number Value
----------------
4 1
5 0
View 5 Replies
View Related
Sep 26, 2011
we can find the IP address in V$session table. Is it possible to find the mac address of user's computer?
View 13 Replies
View Related
Feb 7, 2013
1. I have 6 data bases, one process is running slowly, how do you find which data base that process belongs to?
2. I have 6 data bases, only one data base we have ASM instance, how do you find which data base has ASM?
View 1 Replies
View Related