Select Query Matches Part Of The String

Mar 11, 2010

Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.

Here xxx.a will contain values like 123 or 456 like that .

View 6 Replies


ADVERTISEMENT

PL/SQL :: CTXRULE Matches Non-existing Query String?

May 25, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
==============================================
SQL> select * from books_cat;
     ID CAT               QRY
---------- -------------------- --------------------------------------------------
141 Oracle          {Oracle} OR {RDBMS}
142 Russia          {Russia} OR {Russian}
SQL> select id, file_desc from books;

[code]....

I am learning Oracle Text and created the above routine to check whether Russia/Russian appears in the OracleTextDeveloper.txt file (text version of the standard Oracle documentation - B28303-03).

Some more information: -

DBMS_METADATA.GET_DDL('INDEX','IDCTXR_BOOKS_CAT')
--------------------------------------------------------------------------------
CREATE INDEX "READER"."IDCTXR_BOOKS_CAT" ON "READER"."BOO
KS_CAT" ("QRY")
INDEXTYPE IS "CTXSYS"."CTXRULE"
SQL> select token_text, token_type from dr$idctxr_books_cat$i;

[code]....

View 0 Replies View Related

SQL & PL/SQL :: Query Of Functions - Display Half Part Of String

Jun 8, 2011

I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk

I want to display only the later part of the string

myharbour.org.uk as output.

Can we write using string functions in plslql?

View 19 Replies View Related

SQL & PL/SQL :: Insert Using String Matches / Matching And Searching Match

Jul 3, 2012

i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows

first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.

Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.

Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.

We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.

CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Selecting Only Part Of A String

Feb 25, 2010

I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Extracting Part Of A String

Jan 9, 2013

To make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));

First field content with below record:

FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP

to filter the above record from FLIST column thorugh sql script as below:

FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP

means remove first 5 charator and after APXXXXXXXXX.

Output of SQL query should come like below:

CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION

View 24 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

Reports & Discoverer :: How To Set Part Of String In Bold

Apr 30, 2013

I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.

I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string

for now, i set bold text i want to and i have created a fonction that make text bold like this

l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;

but it doesnt look to works..

how can i do that? And where i can do that?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies View Related

Application Express :: Computation To Trim Off First Part Of String Within List Manager Item Values

Jul 1, 2013

A computation after submit pl/sql function process to trim off the first part of the string (CQ..) within the list manager values. Support for example the list manager contains values such as

 CQ..SAMPLE1..TEST1CQ..SAMPLE2..TEST2CQ..SAMPLE1..TEST2 

The computation process should trim off the first part(CQ..) and should return the list manager value as SAMPLE1..TEST1SAMPLE2..TEST2SAMPLE1..TEST2 Oracle APEX 4.0.2 is the version and Oracle 10g r2 is the database. 

View 7 Replies View Related

Application Express :: Get Part Of URL As Part Of Dynamic Action When Clicking A Button?

Nov 3, 2013

I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?

View 6 Replies View Related

SQL & PL/SQL :: What Part Of Query That Is Being Run Now From Procedure

Apr 14, 2010

I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.

View 23 Replies View Related

Text :: Make Part After AND Not Match Already Matched First Part?

Nov 2, 2012

I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.

The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:

Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;

But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?

View 2 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

SQL & PL/SQL :: How To Find Unique ID When Several Matches

Feb 1, 2012

I have a simple question, but i can't resolve it.

I have a table like this :

ID_A | VALUE
-------------
A 1
A 2
B 1
B 2
B 3
C 1
C 3
D 1

I need to fid the unique ID that match perfectly VALUE = 1 or 2.

I tried SOME/ANY/ALL/IN

View 14 Replies View Related

SQL & PL/SQL :: Linking 2 Tables Without Exact Matches

Mar 4, 2011

I have two tables as per attachement - TABLE_A has Vehicle details while TABLE_B has Address details:

I am trying to build a query in order to link the Vehicle Details with their Address Details.

I would require an SQL that links records in TABLE_A with records in TABLE_B when group_number are the same; however links with group_number '999' (for the same customer_number) if there are no exact matches.

SELECT A.*,B.*
FROM TABKE_A A, TABLE_B B
WHERE ((A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = B.GROUP_NUMBER)
OR (A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER AND A.GROUP_NUMBER = '999'))

The only problem with such query is that record in TABLE_A with group_number '456' will return 2 times. One with address having group_number '456' and one with group_number '999'.

View 8 Replies View Related

Display String Using Select Statement In Oracle 10g

Jul 27, 2010

i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.

example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'

i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.

View 14 Replies View Related

Projecting On Attribute Identified By String In SELECT?

Oct 18, 2008

I'm trying to map a database (with a stupid schema) into an ontology. That's why I need to do such a stupid query (as you will see). Here is the structure of the table:

CodonUs {
ID STRING PRIMARY KEY,
GCT FLOAT,
GCC FLOAT,
GTC FLOAT,
and so on for all possible triplets

What I would like to do is to have a query that joins the organisme table on the ID and extract the name of the triplet (thus, the name of the column) and the exact value:

SELECT ID, 'GCT' AS Codon, Codon

FROM organisme JOIN codonUs ON Abbrev = ID

The result set should be something like:

ID of the organisme | Name of the triplet | Value of the triplet

View 5 Replies View Related

SQL & PL/SQL :: Select Length - String Literal Too Long

Jun 21, 2011

If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.

select length('string with more than 4000 char) from dual;

View 10 Replies View Related

Select Greatest - Difference Between Number And String?

Oct 21, 2012

I am confused with third one.

CODE1. >> Comparing two strings >>
SQL> select greatest('99' ,'100') from dual;
GR
99

2. >> comparing both numbers >>
SQL> select greatest( 99 ,100) from dual;
GR
100

3.>> Comparing string and number >>
SQL> select greatest('99' ,100) from dual;
GR
99

whats the logic behind 99 being returned for thirdone.

View 3 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: Dynamic Sql Query String - Getting Error?

Aug 22, 2010

FUNCTION get_attributed_sedol( p_ref_number IN VARCHAR2,
p_field IN VARCHAR2 )
RETURN VARCHAR2
IS
l_query VARCHAR2(1000);

[code]...

It gives me an error on the line where the execute immediate statement is.
Quote:
ORA-00905
missing keyword

Cause: A required keyword is missing.

Action: Correct the syntax.

But when I check my stack trace table to see what the actual query string looks like, I see this

Quote:
SELECT sedol INTO l_attributed_sedol_code FROM integration.tmp_attributed_sedol WHERE CLIENT_LEDGER_REF='LEAE057090' AND ROWNUM=1

There's nothing wrong with that, is there? It executes fine if I try it manually.

View 3 Replies View Related

How Many Quotes To Use In Dynamic Query Using IN Clause With String

Nov 23, 2011

I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')

My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"

How many quotes are supposed to go around val1 and val2?

I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''

When I run the procedure in Oracle it works with '''val1'',''val2'''

View 1 Replies View Related

Sql Query - Insert Concatenation String To Table?

Aug 6, 2012

i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:

for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....

update trap set TrapNum = (
select trim(both from to_char(substr(TrapNum,1,4),'0000'))||
trim(both from to_char(substr(TrapNum,5,1),'00'))||
trim(both from to_char(substr(TrapNum,6,3),'000'))||
substr(TrapNum,9,2)||
trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap)
where length(Trapnum)=12

but i got error ORA-01427

View 1 Replies View Related

SQL & PL/SQL :: Query To Split Character String Using XQUERY

Aug 5, 2010

I am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.

select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );

View 6 Replies View Related

SQL & PL/SQL :: Convert Column Value To Delimited String Using Query?

Jan 10, 2012

I want to convert a column value to a delimited string using a query.

Example

TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X

Select Col3 From TableA where Col2 = 'x200'

Expected Output:

'MIS-X','BTS-X','TYR-X'

View 4 Replies View Related

SQL & PL/SQL :: Space In Every Character Of A String With Single Query

Mar 19, 2012

How can we get 'space in every character of a string with Single select query'

for example:-
string 'INDIA'
result should be 'I N D I A'

View 1 Replies View Related

SQL & PL/SQL :: Query To Return Value / String If No Records Found

Jun 17, 2012

Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13

I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'

View 2 Replies View Related

SQL & PL/SQL :: Query To Get Whether A String Contains 2 Upper Characters And Numbers Using Regular_exp

Jun 12, 2013

A sample query to check whether a string contains 2 upper characters and numbers are present in that character using regular_exp .

View 12 Replies View Related







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