How To Replace Leading Wildcards

Jan 16, 2011

So many of the queries in our database query by an account but an account is allowed to vary by prefix and so queries are written similar to account like '%suffix'

Our DBA has rejected the use of context indexes and friends to deal with this.

as an aside and I'm probably going to regret going into this much detail but our model is

A->>B->>C

typically the queries want to fetch C's for a customer defined by A.account

A, B, C are all partitioned with partition key created_date

however, only C is typically queried with created_date as a qualifier. A and B are related by joins from A->>B and B->>C

when queries are written to use '%' leading wildcards often it results in a full table scan across multiple partitions. on any given day all we care about are the most recent C's for the customer where customer is defined by A.account.

I recently had an idea whereby fields such as A.Account could also be kept(denormalized) in the C table

the idea is if the queries were written to use C.account like '%bla' instead of A.account like '%bla' then because C is always qualified with the partition key ie. C.created_date that would at worst result in a full partition scan only. this is considerably less expensive than doing a full table scan of A. The IO cost of doing that is huge.

populating the C.account is a simple before insert or update trigger. when you insert a C you have a foreign key to a B and B has a foreign key to A. A and B are always created before any C is seen for any given C. When a C is seen A and B already exist in the database.

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Remove Only One Leading 0?

Jun 3, 2010

I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:

CREATE TABLE leading_0_test
(
col_1 VARCHAR2(10)
)

insert into leading_0_test values('00123')
select trim(leading'0' from COL_1),trim(leading'0' from substr(COL_1,1,1)),COL_1 from leading_0_test

The first trim removes all leading zeros. Therefore I tried to solve it with the substr, but without success.

View 7 Replies View Related

SQL & PL/SQL :: Showing Leading Zeroes In XML?

Sep 15, 2011

I am generating an XML file from plsql using utl_file.There is a problem in number field.The value in the table is 0.18 and it is showing up in xml as .18. The leading zero is not showing up.

COLUMN_NAME is number (15);

<TAG>' || A.COLUMN_NAME || '</TAG>

I have tried using to_char(), but it is still showing up as .18 in XML. I want it as 0.18 in XML as well.

View 1 Replies View Related

Performance Tuning :: Index Leading Columns?

Dec 14, 2010

I have been reading Oracle documentaion about access paths, got strucked at the concept 'Leading columns in index'. what is meant by 'Leading columns of an index', how to find/judge them that they are leading columns.

The url where I am reading is[URL]...

View 4 Replies View Related

SQL & PL/SQL :: Removing Leading Single Quote In Excel File?

Feb 20, 2013

This package is generating excel file which contains cursor result.In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.

eg:

Zip_
'01234
'12567
'23432
'00234

create or replace
PACKAGE BODY PKG_MONTH_END_AUTOMATION AS
PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')

[code]...

-- main body

BEGIN
-- Generating Zip Files
SELECT last_day(add_months(sysdate,-1))
INTO v_last_date

[code]...

View 6 Replies View Related

SQL & PL/SQL :: REPLACE ON USER_MVIEWS / Number Cannot Be Replace

Jul 16, 2010

I have 70 materialized views where I need to replace the FROM SCHEMA1 TO FROM SCHEMA2...To quickly to do the fix for all the 70 views..

SELECT REPLACE (QUERY, 'schema1', 'schema2' )FROM USER_MVIEWS ;

But It throws me an error that Number cannot be replace.

View 14 Replies View Related

Client Tools :: Leading Zeroes Are Getting Wiped Off When Results Are Copied Into Excel

Apr 13, 2012

We wrote a query to spool the results in TAB delimited flat file, Results are ok but when copied into excel the leading zeroes are getting wiped off.

Example:

Result: 0000123 and when copied into excel the value is just 123. I need to retain the value as it is 0000123.

Can we control it in the query itself rather than correcting the excel.

View 4 Replies View Related

SQL & PL/SQL :: Replace Y By N And N By Y

Sep 14, 2010

I want to Update table value N by Y and Y by N.

ex.

col1
y
y
n
n

output:

col1
n
n
y
y

How can i do that ?

View 10 Replies View Related

SQL & PL/SQL :: How To Replace Apostrophe

Jan 4, 2011

I have a table MOM i.e. Minutes of meeting where important points in a meeting are captured. Now these points may include words like, "don't" or "can't" which will be recorded first in a text file and later copied to the table MOM. Rest of the details are unimportant. All I want to know is how do I enter these words without getting the ORA-01756 error? Do I need to always correct them before entering or is there perhaps another way?

View 2 Replies View Related

SQL & PL/SQL :: Replace String In 9i?

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

SQL & PL/SQL :: Replace Comma With '�10'?

Jan 23, 2012

TestCase

DROP TABLE test ;
CREATE TABLE test (id NUMBER, cnt_str VARCHAR2(200));
INSERT INTO test (id, cnt_str) VALUES
(1,'AKRN000002,1451-1473,00000A,74,AKRN000002,1475-14791000000A,8010AKRN000002,1481-1492,00000A,9310AKRN000002,1494-1500')

[code]...

The requirement is in each of the string where there is comma after the number and the number is prefixed by "-" character,
the comma after the number should be replaced by '10'.

For example in the second record where ID = 2,
CNT_STR is '00000B,1-251000000D,26-32,ADTW000301,2858-2875'.
In this string -32, should become -3210 and resulting string should be '00000B,1-251000000D,26-3210ADTW000301,2858-2875'

Expected Result.

ID CNT_STR
-------- -------------------------------------
1 AKRN000002,1451-14731000000A,7410AKRN000002,1475-14791000000A,8010AKRN000002,1481-14921000000A,9310AKRN000002,1494-1500
2 00000B,1-251000000D,26-3210ADTW000301,2858-2875
3 AKRN000001,1126-12001000000B,501-525

View 4 Replies View Related

SQL & PL/SQL :: Replace Column Name

Sep 19, 2010

For example i've a select query as below.

1.select store_name, store_id from stores
2.select store_name, (select store_id from inventory where store_id=<somevalue>) from stores
3. select store_name, store_id from stores
where store_id in(select store_id from stores1)

based on above examples i need to replace all the columns(oly the column names not the column names in the subquery) in a sql query .

is there is any oracle function to achieve this?

and the result shud be as follows if i replace with STORE_DET.

1.select STORE_DET from stores
2.select STORE_DET from stores
3. select STORE_DET from stores
where store_id in(select store_id from stores1)

View 3 Replies View Related

PL/SQL :: How To Replace Execute Immediate In 11g

Jan 18, 2013

creating package and i need not to use execute immediate. There are some dynamic build plsql's and sql's stored in global lists. Any other possibility to run them without execute immediate?

View 3 Replies View Related

PL/SQL :: Replace A String

Sep 26, 2013

I have a string. For example "I have too many files. There are 1000 files. I have to delete them." Sometimes the string can be "I have too many files. There are 115003 files. I have to delete them." Whatever the srting is, I need to change the string to "I have too many files. There are 10 files. I have to delete them." replace the "1000" or "115003" to "10". This portion of the string is always an integer. I use Oracle 11G2. 

View 6 Replies View Related

SQL & PL/SQL :: Replace Repeating The Same Description With -DO-

Dec 19, 2012

I am looking for oracle query to replace repeating description with -DO-. Sample Data is:

CREATE TABLE SCOTT.ITAX
(
VDATE DATE,

[Code].....

VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 CANOLA OIL 3 2500
10-DEC-12 CANOLA OIL 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 CANOLA SEES 3 5500
01-DEC-12 CANOLA SEES 3 5400

6 rows selected.

Required Output is:

VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 -DO- 3 2500
10-DEC-12 -DO- 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 -DO- 3 5500
01-DEC-12 -DO- 3 5400

View 2 Replies View Related

SQL & PL/SQL :: INSTR With Replace Function

Aug 27, 2013

I am trying this function

instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0

i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2

View 1 Replies View Related

SQL & PL/SQL :: Replace Zero With Null Values

Jul 28, 2010

I have a table abc with two column (marks,id) both can have any value.

Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.

how to replace zero with null.

View 22 Replies View Related

SQL & PL/SQL :: Replace Command Is Not Working?

Jun 18, 2013

database 10GR2, Character_set WE8ISO8859P1

The following command not working.

update consumerappliedform
set name =replace (name,'¿','‡');

What is the problem.

View 3 Replies View Related

SQL & PL/SQL :: How To Replace Values Within Loop

Jun 26, 2012

I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.

Well i have achieved this by using case statement but where hardcoding is done...see the case below:-

select substr(ename,1,8),case when length(sal)=2
then '**'
when length(sal)=3
then '***'
when length(sal) = 4
then'****'
when length(sal)=5 then

[code]....

View 27 Replies View Related

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

replace the first 5 commas with the character '|' in the below string:

'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'

I tried:

SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual

but it only replaces the 5th comma.

View 3 Replies View Related

SQL & PL/SQL :: How To Replace Table Name In Procedure

Apr 2, 2012

I need to create a procedure which inserts values from a temporary table into the main table the columns names in temporary table are same for all temporary tables only the table name will be replaced the skeleton procedure code will look like this.

create or replace procedure load_data as

select c_tables is select table_name from user_tables
where table_name like 'TEST%';
V_tbl varchar2(30);
BEGIN
OPEN C_TABLES;
LOOP
FETCH C_TABLES INTO V_TBL;

[code].....

The logic here is to insert values from v_tbl variable table into sales_target table.

when I compile it doesn't like using V_TBL variable in the procedure?

View 5 Replies View Related

SQL & PL/SQL :: Update Using Replace Function?

May 5, 2011

I have a table called email that contains a field called email

I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail

I also have some that are line example@hotmail.co and I want to change those to hotmail.com

so - I want to do a substring search and replace.

can the replace do this for me in one select statement - how do I structure the sql to do this?

View 19 Replies View Related

SQL & PL/SQL :: Replace With The First Occurrence In The String

Nov 12, 2012

I am using regexp_replace function to replace the string between the double quotes with the first occurance but i am able to replace first occurance but I am not getting remaining string.

For example:

select REGEXP_replace('Parent.addChildByName("DS-Id of OAL(BROBA)")||parent.add("DS-Id of OAL(BROBA)")','["]:print:+:punct::print:*["]','XXXX') from DUAL

O/P: Parent.addChildByName(XXXX)

Expected O/P : Parent.addChildByName(XXXX)||parent.add("DS-Id of OAL(BROBA)")

View 4 Replies View Related

PL/SQL :: Replace The Digits After A String CVV With X?

Jul 13, 2012

I am using the oracle 10g, I am trying to write the a sql query by which in a below mention patterns I can replace the digits after a string 'CVV' with X. The no of X will be equal to the no of digits after CVV

1. BTA CVV 8810
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVV498
3. C***CVV VI 569***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV 063/dffddf:dfdfdfd
5. (T) CVV 4671
6. MS-ACEEML/CVV
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES

AFTER a change above data should look like as mentioned below

1. BTA CVV XXXX
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVVXXX
3. C***CVV VI XXX***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV XXX/dffddf:dfdfdfd
5. (T) CVV XXX
6. MS-ACEEML/CVV -- no change for this
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES --- -- no change for this

Initially I tried to find the position of CVV in any string and then from that position take the digit and replace them with X. I tried the below code which is not complete

select REGEXP_INSTR('BTA CVV XXXX','CVV',1,1) from dual;

View 11 Replies View Related

PL/SQL :: Create Or Replace View

Sep 11, 2012

I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.

View 3 Replies View Related

SQL & PL/SQL :: Replace Unique Identifier?

May 27, 2012

How to replace uniqueidentifier in PL/SQL ,I have query like this,

@EmployeeID uniqueidentifier = NULL.

View 11 Replies View Related

Function To Replace All Occurrences Of A String?

Jan 9, 2007

Years ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.

Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.

Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.

I'm thinking something that would work like:

UPDATE table SET column1=REPLACE(column1,oldstr,newstr);

View 4 Replies View Related

SQL & PL/SQL :: Replace Multiple Value In Single Column?

Jun 7, 2011

I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:

"id" type int
"text_data" type varchar2(2000)

Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit

Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.

If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.

View 10 Replies View Related

Client Tools :: Replace & With Any Character

Dec 22, 2010

I want replace '&' with any character.But when i use '&' in select statement.then it ask the value.

View 6 Replies View Related

SQL & PL/SQL :: Replace New Line Character In Clob

Sep 2, 2010

Replacing a new line character in clob column i Oracle 10g.I have tried replacing the new line using regexp_replace,replace and translate. I have also tried using lob_relace procedures.

View 2 Replies View Related







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