SQL & PL/SQL :: How To Do Selective Search Within A String
Nov 8, 2010
Assuming I have the following table with its data:
Quote:
| code | description |
| A | $abc + xyz = $cba - 2 |
| B | ($12345 + $12345)/3 |
| C | $poke + $abc = $abc + 456 - 789 |
How can I do a selective search on "description" column for any word that starts with the "$" sign? In the end, this is the resultset that I am expecting:
Quote:
| $ | count of $ |
| $abc | 3 |
| $cba | 1 |
| $12345 | 2 |
| $poke | 1 |
View 5 Replies
ADVERTISEMENT
Jan 31, 2011
I want to take selective output from String input like
Input:'Six Thousand * Four Hundred Seventy Two Rupees And Zero'
Output:'Six Thousand Four Hundred Seventy Two Rupees'
I am using substr and instr functions but not getting the desired output.
View 22 Replies
View Related
Aug 13, 2012
I have table having a single column a, which contain the values "HP Laptop", can search it in either way , i mean to say either user input the HP Laptop or Laptop HP? is it possible via SQL query ?
View 11 Replies
View Related
Oct 18, 2012
I have one table for eg. TB_Fruits.
In that i have one column FruitsName(Varchar)
In that column i am storing string in comma separated values.
Select FruitsName from tb_fruits;
Result: orange,banana,apple
Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.
Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')
the orange,banana,apple can be in any position.
How to check if any of these names already exist or not in the column fruitsname?
I cannot use like or INstr function here. because the position is not fixed not even string.
View 1 Replies
View Related
Jun 20, 2012
I want to search for a pattern and replace with a string. I can easily achieve the same in oracle 10g with REGEXP_REPLACE , I want to get the similar solution in 9i.
Eg.
I have to search for a string pattern 1234 5678 9012 6736 , I want to replace the same with XXXX XXXX XXXX XXXX.
View 8 Replies
View Related
Aug 14, 2012
My Scenario is'....456re0,50kg400,500rfabs43qre30,25kg150,354rf658....'
there is possible,using regexp_substr or other way to get the values, 0,50 and 400,500 and 30,25 and 150,354? I'm using [^re]+[$kg] and the string comes, but only the first occurence..
View 5 Replies
View Related
Aug 4, 2010
I want to select the view names from text column in dba_views.
View 7 Replies
View Related
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
Oct 31, 2013
How can I search the tree with a given string?I don't find any function like "search(...)".I want to find the tree nodes which include the given string.
View 5 Replies
View Related
Sep 4, 2012
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
View 4 Replies
View Related
Nov 21, 2012
I have a requirement in which i have list pdf files(from thousands of pdf files) having particular string from a command prompt.I have tried find command but that has some limitation. its working upto 3 digit string only.how can i acheive this.remember i have to do this through command prompt only.
View 1 Replies
View Related
Jul 15, 2012
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
View 7 Replies
View Related
Apr 10, 2011
I have tables dynamically created with dynamic number of columns. There will be 7 columns that would always be fixed with their names and data types. They will always be last 7 columns.
way to write a select statement on those dynamic tables where only those known 7 columns are not selected and I need to select all the rest columns but leave the 7 columns names I know.
View 6 Replies
View Related
Mar 13, 2010
I have one query based(Execute_query) tabular form where i pass input parameters and the query results will be displayed.I have around 100 records and i want to display like 15 records per page once i click the button it will go to the next 15th record and theron if i click on that it should move to 30th and i dont want to use scroll bar.Is there any way.I am attaching the fmb.
View 7 Replies
View Related
Dec 11, 2010
I have a Group report based on one group.For eg..there is machine (Group) and it has detail records .The problem is there are certain specific machine on which i want sorting to be done the rest will be not sorted.
machine WX
Details not be sorted
machine sh4
Details to be sorted
machine sh5
Details to be sorted
machine AN1
Details not to be sorted
View 12 Replies
View Related
May 11, 2013
I am trying to export selective data from one of my prod database tables. But not succeeding. I was keep on trying for the past 2 hours.
OS : SOLARIS SPARC
ORACLE - 10G
Query --> WHERE E3RECV_DT LIKE '201305%' (I need to export this query data)
Below Script i am using
===============
exp E3USER@SGEBAPU2 statistics=none consistent=n buffer=100000000 file=exp_pipe_file TABLES=IFDATA query="WHERE E3RECV_DT LIKE '201305\%'" log=PGTB_IFDATA_conditional.log
View 1 Replies
View Related
Aug 4, 2009
I have one 10g database in other country. I want part of their db (selected tables or tablespaces) and import that data to my 10g DB and i want keep to date this data.
I know two ways
1. Data Pump Imp/Emp via FTP, but i can't send only data that have changed (incremental), i must pumping whole selected part of database (i want only new data from their DB, but consistanse with my DB)
2. RMAN etc. or other archivisation tool, i can do incremental achivisation, but can i send files to another instances (my db) and load only that data? Can i do that with SQL*Loader?
View 1 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
Mar 14, 2013
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
View 3 Replies
View Related
May 9, 2008
show an ex to use string buffer for select statemnt
View 1 Replies
View Related
Dec 27, 2009
Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?
View 3 Replies
View Related
Mar 19, 2010
I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables
View 1 Replies
View Related
Jul 5, 2010
Can we search for a column name if we have some value with us, and we are not sure which column it belongs to.
View 3 Replies
View Related
Jan 23, 2012
I am using oracle 10g 10.1.0.2.0 . I want to set lov search value means when we open lov then in search text box it show % but i want to set 1% .
View 5 Replies
View Related
Oct 12, 2012
i have a text field search_part1 in which i put the item for search another text field i send_by which contains items a single item comes in list many times.i have to find the item one by one.i have make this statement for seraching items
Declare
typ varchar2(200);
L_NAME VARCHAR2(50) := :BLOCK3.SEARCH_PART1;
begin
go_block('BLOCK3');
first_record;
loop
if :BLOCK3.SEND_BY = L_NAME then
[code]....
bt the problem is that if a single items comes more than one time then it search only one time and next time it exit.
View 3 Replies
View Related
Dec 27, 2006
I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.
how to do this interactively, without preparing a SQL script with all exisiting tables?
View 2 Replies
View Related
Jan 15, 2009
I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.
I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.
I can not work on stored proc, as I do not have access to create that on server. So, any script will work.
View 3 Replies
View Related
Sep 27, 2010
I have a table emp where empid,employee name,job,salary are three columns.I need to retrieve empid,count of emp,emp name,emp salary of job = manager and total salary of all employees with grouping by job in one query.
View 13 Replies
View Related
Mar 11, 2012
i hav a table in database with name TBH.in that table there are 25 to 30 columns.its a material details table.by using add material form i insert all the details of the material in the table.2 colums have primary key(material id and material code).
now iam making search and update form.on material code item i have WHEN-MOUSE-CLICK-TRIGGER and calling LOV and displaying record.
but the problem is when iam making changes in some fields its giving error ...UNABLE TO INSERT....FRM-40508:ORACLE error:unable to insert record.
when i check display error its showing
ORA-00001: unique constraint (DETA.SYS_C006356) violated..
View 2 Replies
View Related
Nov 11, 2010
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
View 9 Replies
View Related