SQL & PL/SQL :: Numbers To Words

Jul 6, 2012

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

SQL>

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Spell Out Numbers To Words

Mar 14, 2002

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 :

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 ???)

View 12 Replies View Related

PL/SQL :: Verify If Salary In Numbers / Decimals Is Converted Into Words?

Aug 30, 2013

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

View 10 Replies View Related

SQL & PL/SQL :: Extracting Numbers And Few Portion Of Text From A String Containing Dates / Characters And Numbers

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

SQL & PL/SQL :: Amount In Words

Aug 4, 2009

I 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

View 6 Replies View Related

SQL & PL/SQL :: How To Find Two Words Matching

Aug 19, 2013

i 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.

View 6 Replies View Related

SQL & PL/SQL :: Order Words In String

Mar 7, 2013

I 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

View 3 Replies View Related

Insertion Of Any Character After Writing 2 Words

May 3, 2006

How 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.

View 3 Replies View Related

SQL & PL/SQL :: Count Of Repeated Words In A Column?

Mar 1, 2012

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');

[code]....

View 13 Replies View Related

SQL & PL/SQL :: Using Reserved Words As Parameter Names

Mar 18, 2011

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.

View 1 Replies View Related

SQL & PL/SQL :: Code That Display First 10 Words From A String

Oct 19, 2011

wrote a code that display the first 10 words from a string.

View 15 Replies View Related

SQL & PL/SQL :: How To Match Two Words Matching From Two Table

Aug 19, 2013

find 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

View 9 Replies View Related

SQL & PL/SQL :: Update Name With Single Space Between Words

Mar 26, 2010

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

View 6 Replies View Related

PL/SQL :: Regular Expression Matching All Given Words

Nov 8, 2012

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.

View 7 Replies View Related

PL/SQL :: Update Words Based On Soundex

Nov 19, 2012

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

SQL> select soundex(word) from t1;

SOUN
----
L535
E213
L535
G620
M240
M240
L535
E213
L535
G620
G620

11 rows selected.

View 10 Replies View Related

PL/SQL :: Numeric Value In Words (for Cheque Printing)?

Oct 13, 2012

To 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.

View 3 Replies View Related

SQL & PL/SQL :: Regex Delete Repeating Words In A String

Apr 5, 2011

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.

View 7 Replies View Related

Application Express :: PL / PDF Bold Words - Strike-through?

Jan 28, 2013

from those who have created complex reports/forms using PL/PDF. done strike through sentences and words in bold within paragraphs?

View 0 Replies View Related

Application 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?

View 0 Replies View Related

SQL & PL/SQL :: Find Top 20 Most Occurred Words In Particular Text Column

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

SQL & PL/SQL :: Count Number Of Words In A String Separated By Comma

Aug 31, 2011

I 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

View 9 Replies View Related

ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?

Oct 1, 2012

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.

View 12 Replies View Related

String Of Numbers

Oct 12, 2007

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!

View 6 Replies View Related

SQL & PL/SQL :: How To Get Random Numbers

Jul 1, 2010

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 ?

View 3 Replies View Related

SQL & PL/SQL :: How To Get Column Containing Numbers Only

Jul 25, 2010

I have a column of datatype varchar2

this column may contain char or may contain number or may contain both

some values in this column are only number is there any function or any other way to get those column that are only number

View 3 Replies View Related

SQL & PL/SQL :: Combinations Of Numbers

Nov 1, 2010

Earlier, we had a discussion how to generate the permutations. generating the code for combinations

For eg: if I have input as [1,2,3,4,5] then 5c2 = 20 combinations should be generated as pairs

1,2
1,3
1,4
1,5
2,3
2,4
2,5
3,4
3,5
4,5

Total of 10 combinations

View 8 Replies View Related

SQL & PL/SQL :: Function To Sum Five Numbers

Apr 13, 2013

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;

View 7 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I need to find the top two values value for each ID Number:

CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));

INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');

Desired Result:

ID Number 1st 2nd
12345679 30 30
99999999 30 25

View 7 Replies View Related

SQL & PL/SQL :: Numbers To Alphabets

Jul 6, 2013

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

View 23 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

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
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related







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