SQL & PL/SQL :: How To Search For A Particular Text In String Column
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
ADVERTISEMENT
Aug 4, 2010
I want to select the view names from text column in dba_views.
View 7 Replies
View Related
Jun 19, 2012
I'm new to Oracle Text. I want to implement search for the unique ids. Like google search when the user start typing 123 it need to brings anything starting with 123 and has show like entries how google will shows. When I add number 4 to like 1234 then it has bring numbers starting with 1234.
View 2 Replies
View Related
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
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
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
Apr 29, 2011
Is full text search possible in oracle 10g express edition. If so, how is it possible. I browsed many places but couldn't find that.
View 1 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
Mar 3, 2011
We have table with a column REMARKS,where in text somewhere we are storing name also,Now we are planning to update only names with their service number
REMARKS Column Sample row text:
1.It has been created by John
2.John Created this
3.Assistance taken by John for this
Any query which can only replace john in all rows with 1234 .I can update the value if John comes at same position in all rows using SUBSTR() and concat rest of the text.But not getting how to update JOHN in rows at different positions.
View 7 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
Oct 28, 2012
when i insert string text in hebrew i see ????????? how to fix it ?
work on Oracle 11.2 on Oracle Developer using my C# program
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
Aug 9, 2013
I am trying to find a value in any column of any table ? How can i do that ? A value example would be 'FEDERAL' Using Oracle 11g and SQL Developer.
View 6 Replies
View Related
Nov 18, 2012
can we make a search based on column , actually i have one requirement where the data is stored in column as comma seperated values like below
arif,123455,123456,77777
sait,123455,123456,66666
tomm,666666,123455,123456
I want to filter only 123455 and 123456 from this list, is it possible to get a range as i want to use it as report parameters , i can do it by using like operator as below
create table prd_mast ( prd_cd varchar2(12), prd_desc varchar2(3000));
insert into prd_mast values ('1','arif,123455,123456,77777');
insert into prd_mast values ('2','sait,123455,123456,66666');
[Code]....
View 3 Replies
View Related
Mar 15, 2010
in my databse i have column name as email_id
in this column email id of cutomers are stored but in some emaild contains space
i want to find out such email id
View 3 Replies
View Related
Sep 24, 2012
In Application Builder, is there a way to searching the application and replacing text within it?
I have an application item named "FORMAT_TYPE1". I would like to perform a find and replace action (just like a word processor) where any part of the application that has name or text "FORMAT_TYPE1" in whole or in part is replaced by the text "FORMAT_CATEGORY1". For example:
FORMAT_TYPE1 becomes FORMAT_CATEGORY1
&FORMAT_TYPE1. becomes &FORMAT_CATEGORY1.
The find/search would do it in any attribute, source code in the application. There is only a Search Application feature, which is useful in identifying places, but lacks any replace function. Is there something else that will do this, besides exporting, editing the source code in a text editor, and then importing the application again?
View 0 Replies
View Related
Aug 25, 2010
in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function
CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;
[code]....
but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?
View 2 Replies
View Related
Jun 11, 2012
I have a column in a table say ename in emp table, which allows values with newlines and carriage return.
I need to find all the values (i.e ename's) which are having newline chars or carriage return.
View 4 Replies
View Related
Apr 29, 2011
have a performance problem with a simple query, for example:
SELECT *
FROM CA_ADDRESS
WHERE address_k1 = 'L163'
I have an index created in column address_k1. CA_ADDRESS is a 3-4 Million Record table.
I need to improve the performance of the query. How I can improve it?
View 3 Replies
View Related
Apr 28, 2013
I have a requirement that i should list out all the table names which are all using timestamp datatype in a specified schema. Is there any way to find those table names by using any system tables.
View 1 Replies
View Related
Jul 19, 2012
(1.) We have an Oracle 10g DB.
(2.) We want to store PDFs/MS Word docs inside BLOB column in a table.
(3.) We need to show PDF/MS Word content to the user, without opening the PDFs/MS World like we normally do (i.e. Using PDV reader/ MS Word).
(4.) i.e. We need to use SQL statements and
(a.) Get all contents of the PDF/MS WORD doc.
(b.) Search for specific strings inside these docs in the BLOB columns.
View 6 Replies
View Related
Oct 14, 2013
code to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
View 9 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
Jul 19, 2013
I have created a IR report and I set the default search column using the suggestion found in the thread URL....but how can I keep this default even after the research was done?Now it is resetted !
View 1 Replies
View Related
Jul 13, 2011
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
View 7 Replies
View Related
Mar 27, 2013
My requirment is to find the top 20 most occurred words in the perticular text column accross the full table. i WILL have desc column which is plain text where user wites the essay. i have below 2 data example where i have 2 find which word occur most of the time. i have more than 1 million data where i have to perfor this calculation
E.g
INCIDENT_NOINITIAL_SD_FAULT_DESC 70000030 late entering service on the outer rail causing a 4 minute delay. After speaking to the Hainault DDM and the Hainault DMT it has been established that 70000031E105 on entering into platform by one cars lenght applied emergency brakes to stop the train due to a person under his train. Train stopped with two cars still outside the platform. I received a call from Line Controllor at 0823 hrs that a person has jumped in front of train 105 at Greenford. I informed Ian Williamson Train Operations Manager. We traveled on a special taxi with a spare Train Operator at 0839hrs and reached Greenford Station at 0859hrs. Emergency Services ( Police, London Ambulance, London Fire Brigade and ERU)
CREATE TABLE tbl_incd(
INCIDENT_NO NUMBER ,
SD_DESC VARCHAR2(4000 BYTE)
)
data for the above table
Insert into tbl_incd ( INCIDENT_NO, SD_DESC)
Values
(70000031,
'E105 on entering into platform by one cars lenght applied emergency brakes to stop the train due to a person under his train. Train stopped with two cars still outside the platform. I received a call from Line Controllor at 0823 hrs that a person has jumped in front of train 105 at Greenford. I informed Ian Williamson Train Operations Manager. We traveled on a special taxi with a spare Train Operator at 0839hrs and reached Greenford Station at 0859hrs. Emergency Services ( Police, London Ambulance, London Fire Brigade and ERU) were already on site. When spoken to Line Controlllor stated he received a normal radio call from E105 to inform him that he has got a person under his train. At 0826 Line Controllor had traction current switched off between Northolt and Greenford. Train service was suspended from North Acton to West Ruislip in both directions (east and west).
I could not speak to the Train Operator as Police was in the process of interviewing him and he was too traumatised to speak. Trauma Support was arranged from Loughton as we had no one available at West Ruislip and White City. Trauma Support spoke to T/OP over the telephone and Train Operator decided to travel in a special taxi which was booked for him on his own, although a Station Staff was was arranged to travel with him. During the process a spare T/OP stayed with this T/OP. When spoken to Mr. Clack Operations Security Manager stated he was traveling on this train from West Ruislip and made a telephone call to Network Operations Centre at 0826hrs to inform them of the situation. Mr. Clack travelled to the leading cab and confirmed that although the T/OP was badly shaken but he carried out his duties in a professinal manner by placing Short Circuiting Devise in front of the train and second SCD was placed on the rear of the track by Station Supervisor after traction current was discharged. Passengers were detrained by operation butterfly cock. Les Blaxwall Operations Standards Manager was also travelling on this train and offered his to T/OP. Mr. Blaxwall assisted in the wrong direction move and stayed till the end of the operation to assist. Wrong Direction move was carried out by T/OP Martin to assist emergency services for the track search. The deceased was an elderly of Asian origin approximately of 65yrs of age wearing white shirt, beige trauser and white trainers. When CCTV was viewed he was seen pacing up and down on the east bound platform near the waiting room. On approach of T105 he jumped in front of the train at 0824hrs and train stopped with two cars outside the station. The body was removed from platform at 0940hrs by ERU and taken in a body bag to the secure room at Greenford Station. Track was accessed by BTP to carry out track search for any trace of identification at 0932hrs and gave all clear at 0942hrs.
TOM Williamson assisted in closing the train doors by operating the butterfly cock to close position. Traction Current was switched on at 0948hrs and T105 was worked empty to North Acton and reversed to Ruislip Depot. All clear was given at 0951hrs and after station search, Greenford Station was re opened at 0955hrs. E142 was the first train to run in service on the east bound. Body was removed at 1013hrs from Greenford Station and taken to Uxbridge by the police. Trauma Support was offered to Station supervisor by his DSM, but he declined the offer. Service Manager and Line Controllor were kept informed by myself.');
Insert into tbl_incd ( INCIDENT_NO,SD_DESC)
Values
( 70000032'PART OF TRAIN WITHDRAWAL Train 17 had a 6 min delayed departure from White City due to the train having a defective speedometer. Train was changed over and dud train was sent to Ruislip Depot for the attention of the DDMs staff. Ruislip DDM confirmed that the speedometer was defective. This was traced to a defective ATP controller.');
Insert into tbl_incd ( INCIDENT_NO, SD_DESC)
Values
( 70000033,' had a 1 min delayed arrival at Ealing Broadway due to the train having problems at signal EAB2058. The T/op stated that the train was in ATO and was stopped at the red signal (2058). The signal cleared and the train moved forward, the signal went straight back to red and the train stopped. This happened once again. The train did not pass the signal. T/op contacted the Line controller via radio and the signal was cleared, with the train proceeding into the platform at Ealing Broadway. The train was taken to Hainault for the attention of the DDM and a download. Train download and Tracker net confirm the T/op`s account of events. Due to the serious implications of events, an investigation is being undertaken by the Duty Engineer at Wood Lane. The T/op carried out his duties in a professional and correct manner and informed the passengers of the situation as and when required.');
COMMIT;
View 8 Replies
View Related