SQL & PL/SQL :: Code That Display First 10 Words From A String
Oct 19, 2011wrote a code that display the first 10 words from a string.
View 15 Replies
ADVERTISEMENT
SQL & PL/SQL :: Order Words In String
Mar 7, 2013I have given below example.I hope that is clear.
M_STR_1 VARCHAR2(1000);
M_STR_2 VARCHAR2(1000);
BEGIN
M_STR_1 :='CHANDAN,RATTAN,PL,SQL';
M_STR_2 :='PL,SQL,RATTAN,CHANDAN,WINDOWS,LINUX';
--Output should like this same as order of M_STR_1
M_STR_2 :='CHANDAN,RATTAN,PL,SQL,WINDOWS,LINUX';
I think approach should be like take 1st word (before ,)and search the position in M_STR_1
and assign the that position in another variable using ltrim or rtrim. Well this what is i think
SQL & PL/SQL :: Regex Delete Repeating Words In A String
Apr 5, 2011I am trying to delete duplicate or repeating words in a string using regular expressions (regex) and an Oracle database.I have Googled quite a bit on this topic and was unable to track down something that fit the bill or remotely came close to paying the tip.
A pattern would be: word word start with newThe final string should look like this: word start with new Basically all the strings begin with the two words repeating.
SQL & PL/SQL :: Count Number Of Words In A String Separated By Comma
Aug 31, 2011I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
SQL & PL/SQL :: Display All Account Code
Dec 8, 2012I want to display all account Code but i required this account PFKHR01010101010001 to be fixed to have Credit_Amount.
My Structure is
Finance_TRansactions
------------------------------
D_No Char(12);
Account_Code Char(19);
D_Date Date;
DEbit_Amount Number;
Credit_Amount Number;
[code]...
ORA-01427: single-row subquery returns more than one row
Forms :: Display Code Errors Above 10g
Jul 11, 2011I have problem follow: i create a forms 10g and run it then errors. Now, i want to display code errors,how do i do?
View 2 Replies View RelatedSQL & PL/SQL :: If Exist / Then Display As Lines As String Between /
Oct 12, 2010I got some records in the table between the sign / Some of these records are separated by / And I would like to get something like:
If exist / inside the string then display as lines as string between /
For instance:
select handset_brand, handset_model
from the_table;
display : handset_brand: Siemens
handset_model: A35 / A36 / A40 / B35N
I need to get 4 lines:
handset_brand: Siemens
handset_model: A35
handset_brand: Siemens
handset_model: A36
handset_brand: Siemens
handset_model: A40
handset_brand: Siemens
handset_model: B35N
Way To Display Proper Case Of A String
Jan 31, 2012The Ename SMITH should appear as Smith.
View 1 Replies View RelatedDisplay String Using Select Statement In Oracle 10g
Jul 27, 2010i 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.
SQL & PL/SQL :: Query Of Functions - Display Half Part Of String
Jun 8, 2011I 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?
SQL & PL/SQL :: How To Display Missing Item Names For Each Item Code
Feb 16, 2010I have a table with the following data.
item_code item_name
101 Pen
101 Pencil
101 Scale
102 Pen
[Code]...
My aim is to display the missing itemnames for each itemcode.
For each ITEM_CODE there should be four items i.e Pen,Pencil,Scale and Marker If any itemcode missed any of the itemnames those records should display in the output.
So the output should be like this
101 Marker Because the item Marker is missed.
102 Pencil Because the items Pencil and Marker are missed.
102 Marker Because the items Pencil and Marker are missed.
For itemcode 103 no records should be displayed because it having all four itemnames.
Reports & Discoverer :: Bar Code Reader Will Readout Code
Feb 12, 2009I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
View 8 Replies View RelatedSQL & PL/SQL :: Compare SVN Source Code With Production Code In Database
May 30, 2012I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
SQL & PL/SQL :: Type In Code To Make Few Tables Then Run Code
Feb 13, 2012I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
SQL & PL/SQL :: Amount In Words
Aug 4, 2009I am using below code to spell number in words
create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places
[Code]....
The number amount is : 9899.25
I am getting the output is:
NINE THOUSAND EIGHT HUNDRED NINETY-NINE POINT TWO FIVE
But i want output should be
NINE THOUSAND EIGHT HUNDRED NINETY-NINE AND Twenty five FILS ONLY
where i need to modify the code.
Files is equal to cent as per DOLLOR currency
SQL & PL/SQL :: Numbers To Words
Jul 6, 2012I tried to convert numbers to words, it shows the below error.
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 6 11:00:29 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;
TO_CHAR(TO_DATE(2447834,'J'),'JSP')
---------------------------------------------------------------------------
two million four hundred forty-seven thousand eight hundred thirty-four
SQL> SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL;
SELECT TO_CHAR (TO_DATE (244783400, 'j'), 'jsp') FROM DUAL
*
ERROR at line 1: ORA-01830: date format picture ends before converting entire input string
SQL>
SQL & PL/SQL :: Spell Out Numbers To Words
Mar 14, 2002I found a message which explain how to spell out numbers to words. I'm french and the purpose of my question is how to convert numbers to word (to print cheque) but in french the function found in the newsgroup was :
select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
/
I'm afraid but i don't undaunted how pass my number to this function (&num ???)
SQL & PL/SQL :: How To Find Two Words Matching
Aug 19, 2013i am in need to find starting two words matching like i have string "my name is person". i want starting two words find "my name". i tried with this
SELECT T.FULL_NAME,SUBSTR(t.full_name,0,INSTR(t.full_name, ' ')-1+INSTR(t.full_name, ' ')-1) AS outpu2
FROM test t
output: it's giving me the out put but not in proper way som where the second word is cut off and some where first word is not coming.
Insertion Of Any Character After Writing 2 Words
May 3, 2006How can i insert any character or symbol automatically after writing 2 words
E.g.
i want to enter date 20-may-06 , in this i mean it that when i enter 20 then automatically - is inserted.
is there any query in Oracle. Or Oracle Hasn't created this type of query.
SQL & PL/SQL :: Count Of Repeated Words In A Column?
Mar 1, 2012I have a table with the following column and data is like this.
SQL>CREATE TABLE test (
column1 varchar2(50));
SQL>INSERT INTO test VALUES('ABC XYZ');
SQL>INSERT INTO test VALUES('MNO PQR');
SQL>INSERT INTO test VALUES('ABCD ABC');
SQL>INSERT INTO test VALUES('PQR MNOP');
[code]....
SQL & PL/SQL :: Using Reserved Words As Parameter Names
Mar 18, 2011When integrating your product with third-party APIs I've repeatedly come into conflict with PLSQL and Oracle's reserved words list. For instance while integrating the clickatell SMS Message Transmission notification API there are two variables they send.
[URL]......
How do you propose PLSQL handles these requests without using Apache to rewrite the variable names, or using the three and four variable pslql variable collection methods.
SQL & PL/SQL :: How To Match Two Words Matching From Two Table
Aug 19, 2013find two words matching from two different tables.
Example:-
table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis
output:-
john dev || john dev
kab leva || kab leva
SQL & PL/SQL :: Update Name With Single Space Between Words
Mar 26, 2010In the table the names are having diffrent spaces for one name only one space between the words,for another name two spaces between words,for other names three spaces between the words.
I want to update with single space how can we that.
I am giving the data as follows.
CREATE TABLE student(sname VARCHAR2(30));
INSERT INTO student VALUES('PRAKASH BABU');
INSERT INTO student VALUES('RAJESH KUMAR');
INSERT INTO student VALUES('POORNA CHANDAR');
INSERT INTO student VALUES('ANIL RAJ');
INSERT INTO student VALUES('ABHI RAM KONA');
INSERT INTO student VALUES('SRI TEJA MEDI');
Like this the table contains millions of rows.
I want to update the names like this.
PRAKASH BABU
RAJESH KUMAR
POORNA CHANDAR
ANIL RAJ
ABHI RAM KONA
SRI TEJA MEDI
PL/SQL :: Regular Expression Matching All Given Words
Nov 8, 2012I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.
select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');
Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.
search words : one two
one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.
PL/SQL :: Update Words Based On Soundex
Nov 19, 2012I have the following table and data , So i want to update this table(word column) with words that has same soundex with one word of them, the word must be that has the long length if the two words has same length So, update by any of them.
drop table t1;
create table t1 (id number(10) ,word varchar2(100));
insert into t1 values(1,'Londn');
insert into t1 values(2,'Egypt');
insert into t1 values(3,'London');
insert into t1 values(4,'Gorgey');
insert into t1 values(5,'Michal');
insert into t1 values(6,'Michel');
insert into t1 values(7,'London');
insert into t1 values(8,'Egpt');
insert into t1 values(9,'Londan');
insert into t1 values(10,'Gorgy');
insert into t1 values(11,'Gorge');the results must be as following
id word
1 London
2 Egypt
3 London
4 Gorey
5 Michal
6 Michal
7 London
8 Egypt
9 London
10 Gorey
11 GoreyNote: this is the results of soundex
SQL> select soundex(word) from t1;
SOUN
----
L535
E213
L535
G620
M240
M240
L535
E213
L535
G620
G620
11 rows selected.
PL/SQL :: Numeric Value In Words (for Cheque Printing)?
Oct 13, 2012To convert numeric value in words (for Cheque printing), I created two functions in Forms and reports 6i.
1) FUNCTION Spell (val number) RETURN CHAR IS
sp varchar2(100);
BEGIN
[code]...
2) function SPELLED_AMOUNTFormula return Char is
cents number;
c_str varchar2(80);
val number;
[code]...
This convert value up to thousands. How to convert the value more than 1 lac.
Application Express :: PL / PDF Bold Words - Strike-through?
Jan 28, 2013from those who have created complex reports/forms using PL/PDF. done strike through sentences and words in bold within paragraphs?
View 0 Replies View RelatedApplication Express :: Highlight Words - Only First Occurrence?
Feb 14, 2013'm using the "Highlight Words" column formating for a report. There, I'm using the item syntax: &P1_RENVOI. Problem is, when the report appear, only the first occurence of the value of item "P1_RENVOI" is in red for every field in that column.
Is this a normal feature or should all occurences be put in red?
SQL & PL/SQL :: Find Top 20 Most Occurred Words In Particular Text Column
Mar 27, 2013My 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;
PL/SQL :: Verify If Salary In Numbers / Decimals Is Converted Into Words?
Aug 30, 2013How do i check in Table B whether it is converted correctly into words taking input or reference from table A
Consider below example:-
Table A Table B
$125 Dollar One Hundred twenty Five only
$45,542 Dollar Forty Five Thousand Five Forty Two Only
$145.56 Dollar One Forty Five and fifty six cents Only
$145,253
$35,256.65
$560,250.67