SQL & PL/SQL :: Using INSTR In Where Clause To Give COMPLETE MATCH Strings?

Dec 9, 2011

We have a SSRS Front end screen which sends multi-select column values as comma separated strings to back end ( Oracle 10g) procedure .

The procedure builds the string by inserting single quotes in the following manner.

P_BU_LST is the parameter which have comma separated values

'1234,3456,4577' i.e, BU ids selected by user in front end
the procedure inserts single quotes to this paramer value

i.e., '1234','3456','4577
v_bu_lst := '''' || REPLACE(v_selbu, ',', ''',''')|| '''';

This is used the where clause of the REF CURSOR SELECT query which send the data back to SSRS

ie.,
SELECT BU.*
FROM BU_DETAIL BU
WHERE INSTR(V_BU_LST,BU_ID) <> 0;

INSTR has a chance to fail in this scenario if the value send from the front end is 123456,3456,4577

here 123456 does not exist in table, but it will be true for INSTR and values 1234 from table will be send back to SSRS which is wrong. Earlier I was using a function to convert the comma separated values to multi-rows and treat it like a lookup table.

But the main table has around million records , and each row has to processed against each row of lookup table, which makes it slower. To avoid this I used INSTR which is faster but can give wrong results.

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Instr Versus DBMS_LOB.instr / Search A Pattern Backwards In A CLOB Field?

Mar 10, 2012

I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.

Parameters: instr(text_to_be_searched, pattern, offset, nth)

Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.

select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;

View 6 Replies View Related

SQL & PL/SQL :: Where Clause - Literal Does Not Match Format String

Feb 28, 2011

In query I have WHERE clause like this:

WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') >
TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')

I have data like this:

PEROFOPFROMAIRCRAFTSTD

29/03/20102150
NULL NULL
NULL NULL
30/03/20102150

When I execute the query it always gives me the error "literal does not match format string".

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

How To Use Substr / Instr To Isolate

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

View 3 Replies View Related

SQL & PL/SQL :: Instr Function And Index Usage

Apr 14, 2012

I have a requirement where the user input values will be passed as comma separated string to backend, which is queried against the table using instr. But the index present on the table is not using the index , due to the instr function.How can I create a index in such a way that The instr function uses it.

CREATE TABLE TEST_IDX
(
CCN VARCHAR2(10 CHAR),
SKU_NUM VARCHAR2(10 CHAR),
MOD VARCHAR2(10 CHAR),
SKU_STATUS VARCHAR2(2 CHAR),
RPT_FLAG VARCHAR2(2 CHAR),
CYCLE_AS_OF_DATE DATE,
SMP_IDENTIFIER NUMBER,
MEASURE_NAME VARCHAR2(100 CHAR)
);
CREATE INDEX TEST_IDX1 ON TEST_IDX
(CCN, SMP_IDENTIFIER, MOD, CYCLE_AS_OF_DATE, RPT_FLAG,
MEASURE_NAME);

The below query is going for full table scan due to this.

select * from test_idx where (INSTR (','||'E10000'||',', ',' || ccn || ',') <> 0 OR 'E10000' = 'DEFAULT')
and mod='90396' and rpt_flag='O' and smp_identifier=2

how to recreate the above index so that these queries uses this index.

View 16 Replies View Related

How To Make INSTR Search DISTINCT

Feb 9, 2010

I have a script that is using the INSTR function to search through a block of data for a specific string (CALL). I am ONLY looking for that string set but unfortunately, there are other words within that block of text that have that string set within it (e.g. CALL_MY_PHONE). Is there any way to make the INSTR search DISTINCT? Below is the code that I am using:

to_number(DECODE(INSTR(ph.block,'CALL'),0,0,1))

View 3 Replies View Related

SQL & PL/SQL :: Substr And Instr In Update Statement

Nov 28, 2010

I have a table1 with columns serial no and name as below

serial no name
1 john paul
2 john victor

and i have another table1 with columns serialno,firstname and second name as below

serial no firstname secondname
1
2

I want to update my table2 with the name from table1 and divide and insert in first name and second name as below

serialno firstname secondname
1 john paul
2 john victor

i use the query as below

update table2 set
firstname = (select substr(name, 1, instr(name, ' ', 1) - 1)
from table1
)

but the above query not worked.

View 11 Replies View Related

SQL & PL/SQL :: Search String From The End To Beginning (reverse INSTR)?

Sep 22, 2010

Trying to get a number out of an error backtrack

06512: at "SCHEMA.PROCEDURE", line 4

I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.

The database languages might differ so I can't do this using

v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)

As it would not find any 'line' inside the string.

View 7 Replies View Related

SQL & PL/SQL :: SUBSTR / INSTR - Isolate MVNAME Schema?

Aug 28, 2012

i need to isolate the MVNAME schema from the following job;

dbms_refresh.refresh('"SCHEMA"."MVNAME"');

how to use substr/instr to isolate it?so far I have

select substr(what,instr(what,'"',-1,2),15) from dba_jobs;

But its proving a bit tricky to nail it.

View 6 Replies View Related

SQL & PL/SQL :: To Compare Two Strings

Apr 8, 2008

I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match

Do we have any oracle built ins to compare ?

View 6 Replies View Related

SQL & PL/SQL :: String Between Two Strings?

Apr 4, 2012

I have to fetch a string which is between to constant strings in a column.

Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.

In this i need to fetch only ID from each row.

create table testtable ( TestCol varchar2(4000));

INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');

View 17 Replies View Related

PL/SQL :: How To Split Strings

Oct 18, 2013

PROCEDURE COLUMN_SPLIT (p_def   IN VARCHAR2, p_sch  OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2) 
IS  
BEGIN 
NULL;  
END;
END; 

I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.

View 13 Replies View Related

Sorting Strings As Integers

Jun 10, 2007

The db field is a string-type field that hold strings such as:

'1234'
'753'
'textstring'
'345'

Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'

My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'

Is there a quick and dirty SQL-only way to doing this in Oracle?

View 3 Replies View Related

Trimming Strings - Comparing?

Jul 26, 2007

I have 2 strings that I want to compare for example

string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'

There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?

View 2 Replies View Related

SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

PL/SQL :: Embedding Quotes In Strings?

Aug 16, 2013

In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it. 

SQL> select 'This isn''t' from dual;
'THISISN''
----------
This isn't
SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual;
TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''')
---------------------------------------------------------------------
                                                            34456.789
SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual;
TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY')
------------------------------------------------------
Today's 16th August 2013

View 12 Replies View Related

PL/SQL :: Extract Values Of Strings?

Jun 20, 2012

extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.

WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Comparing Strings Entered In A Form?

May 27, 2011

I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )

But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.

So on comparing, it fails as the PL/SQL compares it with English Strings.

View 4 Replies View Related

SQL & PL/SQL :: Splitting Given String Into Multiple Sub-strings?

Feb 18, 2013

I need to split the given string into muliple sub strings based on one special character

Ex : Speace is Special character

with data as (
select 'ab cd ef gh ' from dual )
select * from data

Required Output :
ab
cd
ef
gh

View 3 Replies View Related

SQL & PL/SQL :: Processing Comma Separated Strings

Mar 3, 2003

Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?

View 4 Replies View Related

SQL & PL/SQL :: External Table And Blank Strings

Sep 23, 2011

I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.

CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';

CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......

This is an except of what's in the external text file. The full text file has been attached.

000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------

Here is the error message I am receiving. I believe this is caused by the blank fields in the data.

INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52

This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?

View 12 Replies View Related

SQL & PL/SQL :: Insert Query Having Strings As A Column

Dec 13, 2012

tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

getting missing comma with the above query.when i tried to give as

insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')

insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"

how to avoid this and get the select query to store as

select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij

View 5 Replies View Related

SQL & PL/SQL :: Convert One String Into Multiple Strings?

Aug 31, 2010

i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.

View 20 Replies View Related

SQL & PL/SQL :: Updating Another Table For Equal Strings

Jun 8, 2010

There are two tables:

create table songs(song_name text, song_artist text,song_url text, song_cat text, last_edit text);
create table categories(cat_name text, cat_total int);

im trying to create a trigger that, when i insert a new song in the songs table, it will check the category of the song (song_cat) and increase the respective cat_total (from table categories) by 1.

here is what i've done so far:

drop trigger countcat;
CREATE TRIGGER countcat AFTER INSERT ON songs FOR EACH ROW
update categories SET cat_total= cat_total +1
WHERE cat_name = (select song_cat FROM inserted);

What to write in the cat_name = (select ...). I have tried lots of stuff but still nothing. when i use this, i get the error that mydatabase.inserted doesnt exist

View 4 Replies View Related

SQL & PL/SQL :: Package Returning Multiple Strings

Apr 5, 2011

I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.

I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?

My Package is the next one:

create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;

create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Divide Sting Into Small Strings

Aug 7, 2010

I have a string:

"This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876.
This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090.
This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3.
This is just for testing 34983245983.
This is just for testing 6432."

I need to divide this sting after every 100 characters, as the length of column to insert is 100. And i do not want to modify the column as it has great impact. I need to divide the string, such that it should be less then 100 characters also the string is not cut in between.

like:
first string: "This is just for testing 123.
This is just for testing 45654.
This is just for testing 5567876."

then 2nd string: "This is just for testing 53456547.
This is just for testing 123423.
This is just for testing 98090."

then 3rd string: "This is just for testing 099473.
This is just for testing.
This is just for testing.
This is just for testing 3."

View 6 Replies View Related

SQL & PL/SQL :: Give New ID To Existing Employee?

Mar 13, 2012

want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :

1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).

The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.

Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.

When step 2 ends, we release all the tables from the markers.

My simple question : how to achieve this ?

View 12 Replies View Related

Forms :: How To Give Hyperlinks

Jan 14, 2012

i would like to give a hyper link in forms. how it possible, any way to do it.

View 8 Replies View Related

PL/SQL :: Can Give Name To Block Of Code In SP

Aug 29, 2013

Version- 10G I have a stored procedure which contain code like

if () then---update---else if() theninsert --update--------else if()-----------------------end if;end if;end if; 

I have to use this code numbers of times in my procedure , is there any way I can give name this code & use it or call it where I need it in my SP.Don't want to make the above code a separate procedure. 

View 4 Replies View Related







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