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

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 View Related

SQL & PL/SQL :: Double Right Outer Join

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

PL/SQL :: Representing Double Space?

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

SQL & PL/SQL :: Insert String Having Two Double Quote?

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

SQL & PL/SQL :: Show Value Of Variable In Double Quotes?

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

SQL & PL/SQL :: Procedure To Update Double Subcategory Sequence

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

SQL & PL/SQL :: Column Type For Double Byte And Image

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

Forms :: Printing Double-Sided Using SET_REPORT_OBJECT_PROPERTY?

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

Server Utilities :: Loading String Has Double Quotes In It?

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

SQL & PL/SQL :: Remove Spaces By Excluding Double Quotes From A String

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

SQL & PL/SQL :: Double Quotes In Column Name Can Be Replaced With Alternate Character?

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

Server Utilities :: SQL Loader Loads All Fields With Double Quotes Into Staging Table

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

SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters

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

PL/SQL :: Finding The Max Value

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

SQL & PL/SQL :: Not Finding The Foreign Key?

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

PL/SQL :: Finding ID Max (salary)

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

Finding 10 Most Recent Missions

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

Finding Alert Log File

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

Finding Primary Key In A Table?

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

Finding Unused Index

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

Finding Permission Tables

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

SQL & PL/SQL :: Finding The Difference Between IN And EXIST?

Nov 22, 2011

finding the difference between IN and EXIST.

View 15 Replies View Related

SQL & PL/SQL :: Finding Session IDs Initiated By Particular Job

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

SQL & PL/SQL :: Finding Values In A Range?

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

SQL & PL/SQL :: Finding Duplicate Records

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

SQL & PL/SQL :: Finding The Corresponding Index Partition?

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

SQL & PL/SQL :: Finding Value Of Bits From Hex Number?

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

SQL & PL/SQL :: Mac Address Finding Using Oracle

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

Finding Correct DB Process?

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







Copyrights 2005-15 www.BigResource.com, All rights reserved