I 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
I 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 :
How 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
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
I 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');
When 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.
In 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
I'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.
I 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
I 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.
'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?
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;
I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.
HERE
select a.* from DOC a , FOLDER B where a.NUMBER= B.INCIDENT_ID and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.
I have the following set of numbers that i am passing in as one input into a stored procedure.
234,456,234,456,567
Now i want to take this list of numbers and use it in an IN statement:
select * from table where column_a in (P_INPUT);
however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!
I want to get 10 random numbers from existing 100 numbers. How can we get/generate random numbers ?
for example I have a table with customer ID, customer Name, having 100 record. We want 10 customers ID randomly from that 100 record not repeated any number. Have any command or procedure for that ?
I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?
Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP
CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER) RETURN NUMBER IS Sumnums NUMBER; BEGIN SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL; DBMS_OUTPUT.PUT_LINE(Sumnums); RETURN 1; END sumfivenumbers; / SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;
I have a requirement to display numbers as equivalent alphabets , like if the stored value is 1 then it should display as 'A' ,2 means 'B' ,is there a way to find out.
CREATE TABLE APS ( ITEM_NO NUMBER, ITEM_NAME VARCHAR2(12)) INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (1,'TEST1'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (2,'TEST2'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (3,'TEST3'); INSERT INTO APS (ITEM_NO,ITEM_NAME) VALUES (4,'TEST4');
-- The output to be is.
item_no , item_name A TEST1 B TEST2 C TEST3 D TEST4
Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj' , '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .) '|' || -- or '(.[0-9]+)' -- starts with decimal point ) AS result FROM dual ;
but mean i have to add . after numbers . as follow
SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj' , '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .) '|' || -- or '(.[0-9]+)' -- starts with decimal point ) AS result FROM dual ;
