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


ADVERTISEMENT

SQL & PL/SQL :: Extract Numbers From Text / Multiply / Get Result

Feb 27, 2013

Have 2 small tasks for regexp..where we need to extract numbers from the text & multiply & get the result.

Input field varchar2 : 5x a day x 10 days
Output : 5 * 10 = 50
Select regexp_replace('5x a day x 10 days', '[^[:digit:]]' from dual;

The code extracts numbers ..but doesn't multiply & give the result.Have one more scenario as well.

Input field varchar2 : take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days
Output : 2 * 800 * 4 * 6 * 10 = 384000
Select regexp_replace('take 2 tablets (800 mg) by oral route every 4 hours while awake for 10 days', '[^[:digit:]]' from dual;

For the above code, if it's hours ..we need to convert into day by multiplying with the required factor to make it a day.

View 9 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

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

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 View Related

SQL & PL/SQL :: How To Print Even Numbers Without Using MOD Function

Mar 1, 2012

How to print even numbers 1 to 10.without using the MOD Function

View 17 Replies View Related

Insert All Client Numbers

May 12, 2010

I am trying to run this script:

Update ACT.CUSTOMER_CLIENT_REFERENCE
Set ORIGINAL_SOURCE_FG = 'N',
CANADIAN_ULTIMATE_REFERENCE_FG = 'N',
LUDT = SYSDATE,
CLT_NO = (Select clt_no from client.client where clt_no between 701885 and 705287)
Where Exists (Select 1 from client.client where clt_ofc_no = 19 and clt_no between 701885 and 705287)

But the CLT_NO = .... line is giving me this error: ORA-01427: single-row subquery returns more than one row

what i am trying to do is insert all the client numbers between 701885 and 705287 into ACT.CUSTOMER_CLIENT_REFERENCE

View 6 Replies View Related

SQL & PL/SQL :: Checking Consecutive Numbers

May 11, 2012

I have a table with three columns: terminal, place and batch. How can I check for missing batches?

select * from transactions;

terminal place batch
84812
84813
84814
84816
84821
84823
84824
84825

View 5 Replies View Related

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

SQL & PL/SQL :: Missing Numbers In A Sequence

Feb 16, 2010

create table t (
number_from number,
number_to number);
insert into t values(1,3);
insert into t values(5,9);
insert into t values(10,15);
commit;

I need to create a stored function that could find/return missing numbers between number_from and number_to for each record in table t.

For example:

Number_fromNumber_to missing

13
594 from previous record, this record is supposed to start from 4

View 24 Replies View Related

Forms :: Calculation When Add Numbers

Feb 5, 2011

I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example

5.24 Plus 5.24 it will give the result 10.48

I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12

View 7 Replies View Related

SQL & PL/SQL :: Find Numbers Between More Than 1 Condition

Sep 21, 2011

I have a table A with 10000 records with values as 1 to 10000. Table B has columns as range,number1,number2. Totally there are three rows as

ROW1=range1,1,100 & ROW2=range2,300,500 & ROW3=range3,800,900

I need to select all records from table A when it is between any one of the three rows from table B.

View 13 Replies View Related

SQL & PL/SQL :: Assigning Numbers To The List?

Apr 10, 2012

acheive the below result.

Select * from result;

res
===
3
3
3
3
3
0
3
3
3
0
3
0

output:

I would like to get res1 using SQL query.

resres1

31
32
33
34
35
00
31
32
00
31
32
33
34
00
.. ....

View 9 Replies View Related

SQL & PL/SQL :: No Rounded Decimal Numbers

Aug 5, 2011

how can show this query in this picture

select round(15.555,2) from dual appear final result 15.55 only

and this query don't be this result 15.56

because i have filed and always Decimal numbers be (15.555,2)

i want only result appear after two numbers and no rounded the numbers

View 2 Replies View Related

PL/SQL :: Compare 2 Numbers With Different Precision

Jan 30, 2013

Using DB 10.2.0.5, I've encountered a strange behavior today while trying to compare ORA_ROWSCN with a previous SCN stored in a column.

SELECT
h.id HID,
h.ora_rowscn HSCN,
o.id OID,
o.scn OSCN,
[code]......

1157 rows While casting o.scn to a number gives me another resultset (this one is correct):

SELECT
h.id HID,
h.ora_rowscn HSCN,
o.id OID,
o.scn OSCN,
h.ora_rowscn-o.scn DIFF
[code]......     

2114 rows I got the same result if I use NVL(o.scn,0) rather than TO_NUMBER(o.scn). I can't find out why this happens. Obviously, the ">" condition doesn't match when the difference is too small.

AFAIK, ora_rowscn is a NUMBER while my "scn" column is a NUMBER(12) (which should be sufficient to store my DB or remote DB's SCN).

View 9 Replies View Related

SQL & PL/SQL :: Missing Numbers ( 1 To 2226 )

Oct 16, 2011

I have a table named tbl_student which contains 2226 rows have a column student_code of datatype number which contains student number in serial order from 1 to 2226.

I want to know what numbers between 1 to 2226 are missing.Is there a way in SQL by which I can query the above table for the desired result. There were 2246 records in all which means 20 unknown numbers (between 1 and 2226) are missing. No records have been deleted.

View 9 Replies View Related

SQL & PL/SQL :: Oracle - Generating Combination Of Numbers?

Oct 25, 2010

I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]

I want to get various combinations of numbers with these three digits.

writing the stored procedure generating the output for this

View 18 Replies View Related

SQL & PL/SQL :: How To Show Sequential Numbers For Each Group

Oct 4, 2010

MyTable has missing records:

id_group id_column
-------------------------
1 1
1 2
1 3
1 5
1 9
2 1
2 2
2 4
2 6

As you see, it could be missed record 4,6,7 and 8 of group 1 and record 3 and 5 of group 2. That's is an example.

I need to show all sequential records, incluing missing ones, like this:

Id_group Id_column
-------------------------------------------------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
2 1
2 2
2 3
2 4
2 5
2 6

I try this query:

/*---*/
Select G.id_group, C.rownum id_column
from (select distinct id_group from MyTable) G
cross join (SELECT rownum FROM dual CONNECT BY LEVEL <= (select
max(id_column) from MyTable)) C
/*---*/

but it shows record 7, 8 and 9 as members of group 2.

View 9 Replies View Related

SQL & PL/SQL :: Calculate Difference Between Multiple Numbers

Feb 28, 2013

I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.

create table table_1 (
col1 varchar2(5),
col2 number(6,0),
col3 number(12,0)
)

insert into table_1 values ('AAA', 34379, 11111);
insert into table_1 values ('AAA', 39032, 11111);
insert into table_1 values ('AAA', 54337, 11111);
insert into table_1 values ('AAA', 78005, 11111);

insert into table_1 values ('AAA', 66793, 22222);
insert into table_1 values ('AAA', 74323, 22222);
insert into table_1 values ('AAA', 81426, 22222);

Expected Output:

col1 col2 col3
AAA -4653 11111
AAA -15305 11111
AAA -23668 11111
AAA -7530 22222
AAA -7103 22222

View 6 Replies View Related

SQL & PL/SQL :: Function For Allowing Only Numbers And Characters

Jan 10, 2011

Need to write a function that should allow only characters and numbers in Login ID using pl sql.

View 7 Replies View Related

SQL & PL/SQL :: Searching Numbers In Description Column?

Jun 15, 2010

I have two tables emp and dept

sql>select emp_id from emp;
emp_id
------
123
345
6782
32

[code].....

I would like to search all the employee id's which are present in description column of dept.

View 32 Replies View Related

SQL & PL/SQL :: Need To Check Pattern Matching Numbers

Apr 19, 2010

I am trying to write a pl/sql script where i need to check pattern matching numbers.My database is oracle 10g and i will put this logic in a procedure.i will pass no of tel_no to get.

if the no is 3 then i need 2072860126, 2072860127 and 2072860128(i.e all 3 in sequence)
if 2 then 2072860126, 2072860127. as such..the selected nos must be in sequence.

A query returns list od tel nos. in that tel i need to choose which staisfy my criteria.

View 1 Replies View Related

SQL & PL/SQL :: Generate Unique Numbers Without Sequences

Dec 20, 2010

i had a requirement to generate unique numbers without sequences.

For Example: i had a table called "Test".Test has two columns.

ID and Name are 2 column names.Primary key constraint exists on column(ID).

How to generate the ID values without using sequence and Stored procedures.

View 9 Replies View Related

Exadata :: How To Get The Serial Numbers From Switches

Mar 20, 2013

My management is wanting to know the serial numbers of all the components of our two Exadata machines: one quarter-rack V2 and one half-rack V1

I can use dmidecode to get the appropriate information for comp nodes and storage cells, but not for the Cisco/Voltaire switch, nor the IB switches. I read MOS 1299791.1 and the thought of asking a DOC operative to pull out the label ‘which could damage the switch’ worries me quite a bit.

Is it still true that we are unable to obtain the serial number for the IB switches and the Cisco/Voltaire switches from the CLI of the switches themselves? Sad face.

View 8 Replies View Related







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