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


ADVERTISEMENT

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

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

SQL & PL/SQL :: Subtract Two Numbers And Convert Result As Hours And Minutes

Oct 6, 2010

I want to convert numbers into hours and minutes.I have two numbers say like first number is 1234 and second number is 1235.4 now i want to find the different between these two numbers. so am subtracting number 2 - number 1

1235.4 - 1234 i will get 1.4 so the result of this 1.4 to be converted as hours and minutes like 1 hr and 40 minutes. How can i convert numbers in hrs and minutes.

View 7 Replies View Related

Extract Data From Oracle To Text Files?

May 13, 2004

I would like to extract some data value from oracle to a text file...and i m not sure how to set the delimiter between the columns data value

SET echo off
SET space 0
SET pagesize 0

SPOOL a.txt

SELECT emp_id, name, add
FROM table1
/
SPOOL OFF

Where do i set the delimiter?

Can i do something like in SQL*Loader?

fields terminated by ',' enclosed by '"'

I would like the text file to be display as
"123","ABCD","123 abc road"
"234","XYZ","234 xyz road"

View 5 Replies View Related

PL/SQL :: Extract Values From Text In Reporting Environment

Jun 26, 2012

I need write a query based on a bunch of user supplied IDs. The IDs will be pasted as plain text, one per row, by end user in a memo field in the reporting environment, and I need to do something like this:

SELECT PHONE_NUMBER FROM TELEPHONE
WHERE
ID IN('MEMO_ID1', 'MEMO_ID2',.....)

Reporting environment does not provide any tools to automatically convert plain text into IDs.

View 9 Replies View Related

SQL & PL/SQL :: How To Extract Full Text From Clob Where Having Distinct Select

May 23, 2013

I am only able to extract only 4000 characters from the clob column "DESCRIPTION".how to get more characters or max for that column with the same query concept?

select distinct
o.id "Organization ID",
en.entity_id "Contact ID",
en.entity_cd "Note Entity Code",
ed.entity_name "Note Entity",
en.entity_id "Note Entity_id",

[code]....

View 28 Replies View Related

SQL & PL/SQL :: Extract Data From Local Database Table To Text File?

Jun 27, 2013

i want to extract data from my local database table to text file using plsql

View 4 Replies View Related

SQL & PL/SQL :: Extract Email ID From A String / Text Using Oracle Regular Expressions

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

Forms :: Getting Calculation Result From 2 Text Boxes?

Mar 8, 2011

I'm creating an on demand software. I have 3 text boxes on my form

1st text box will carry Product's Actual Price
2nd text Box will carry Product's Selling Price
3rd text Box will carry Profit..

3rd one is display item..this will carry "selling price - actual price" and then result into 3rd box.. that would be profit..

my query is select nvl(:PL_PRODUCT_SELL_PRICE, 0) - nvl(:PL_PRODUCT_ACT_PRICE, 0) INTO :PL_PRODUCT_PROFIT FROM SBM_PRODUCT_LIST;

how can I move Proceed? I've currently applied this trigger as POST-query on form and POST-CHANGE on :PL_PRODUCT_PROFIT

View 3 Replies View Related

Client Tools :: SQL For Saving Query Result In A Text File

May 17, 2012

I want to execute a query which automatically saves the result of a set of queries in a text file and have to send that as an attachment thru a mail (using UTL_MAIL or UTL_SMTP) thru a stored procedure...saving the result in a text file in Toad.

View 2 Replies View Related

Client Tools :: Exporting Result To Text File Through Query?

Dec 12, 2012

I'm running this query on sql developer trying to export large file but its not executing.

set head off
spool c:myoracle.txt
select txt_name_insurer||'~'||txt_policy_number from Table_Name where rownum<'10';
spool off
set head on

Error:- line 1: SQLPLUS Command Skipped: set head on

View 16 Replies View Related

Client Tools :: Exporting Result Into Excel Or Text File?

Jul 2, 2012

i'm working on sql developer my table contains 40 columns and contains around 4 to 5 lakhs records........

when i'm trying to export the results into excel or text file my sql developer is getting hanged... if the result is less than 2lakh record its copying....

View 6 Replies View Related

PL/SQL :: Multiply With Previous Row Value?

Aug 21, 2012

i need to multiply with previous row value?

my table have about to 100 columns and lakhs of rows

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
12/1/2011 0.5
8/1/2011 0.5

[code]....

My requirement is multiply the existing adj by adj

adj value coming as 0.5
present year always 1

Ex:

date adj
------ -----
8/21/2012 1
1/1/2012 1
.
.
12/1/2011 0.5
8/1/2011 0.5

[code]....

View 8 Replies View Related

SQL & PL/SQL :: Multiply Rows From Different Tables?

Oct 27, 2011

I have the task that I have to determine the number of parts that need to be produced based on the number of products sold for the day (each product consists of many parts).

I am using SQL 11g Express.

The report would look something like this:

{OrderDate PartID PartDesc NumOfParts(Total for that day)
10-24-2011 2001 12" X 12" Solid Shelf 108
10-24-2011 2003 12" X 24" Solid Shelf 32
10-24-2011 3001 96" Side Panel 50

[code].......

My issue is, I can't get the equation right to produce the total number of parts. I think I need to multiply ProductPart.NumOfParts by SUM(CustOrder.Qty) Group by CustOrder.SKU.

Below I have what the calculations should look like

SKU PartID Total Parts Needed by SKU
DVCK1212 1001 42 (7 orders * 6 shelves)
DVCK1812 1002 0 (NONE)
DVCK2412 1003 42 (7 orders * 6 shelves)
DVCK3012 1004 78 (13 orders * 6 shelves)

[code].......

Here's my script:

/**PRODUCT**/
CREATE table Product
(
SKU VARCHAR2(10) NOT NULL,
ProdDesc VARCHAR2(50) NOT NULL,
Price NUMBER(5,2),
PRIMARY KEY(SKU)
);

[code].......

Query 1

SELECT ProductPart.NumOfParts
FROM ProductPart,
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder

[code].......

Result: I am only getting a total of 2 parts

Query 2

SELECT ProductPart.Qty,
ProductPart.PartID,
ProductPart.PartDesc
(SELECT COUNT(CustOrder.SKU)
FROM CustOrder
GROUP BY CustomerOrder.SKU)TotalProducts,

[code].......

ORA-00936: missing expression

Query 3

SELECT Part.PartDesc.
ProductPart.NumOfParts
FROM ProductPart, Part
( SELECT CustOrder.SKU,
sum(CustOrder.qty) cust_qty_sum
FROM CustOrder

[code].......

ORA-00933: SQL command not properly ended

Query 4

SELECT o.OrderDate AS "Date Ordered",
o.OrderDate + 5 AS "Date Due",
pp.PartID AS "Part No.",
pp.NumOfParts * COUNT(o.SKU) AS "Qty"
FROM CustOrder o

[code].......

ORA-00934: group function is not allowed here

View 2 Replies View Related

SQL & PL/SQL :: Writing A Query To Multiply Columns

Nov 26, 2012

I am writing a query and I am trying to multiply some of the columns with *1.50 and 0.75.

The columns I am trying to multiply are coke_rebate.volumecsd and coke_rebate.volumencb. I am getting an error in oracle sql command from web "ORA-00937: not a single-group group function"

Here is the code I am writing:

SELECT CUSTOMERS.CUSTID,
CUSTOMERS.MEMBERID,
CUSTOMERS.BNAME,
COKE_REBATE.COKEID,
SUM(COKE_REBATE.VOLUMECSD*1.50) "TOTALCSD",
[code]........

View 7 Replies View Related

SQL & PL/SQL :: Function That Multiply Number When There Is More Than 3 Decimal Point?

Mar 20, 2010

how to create a function that will multiply the number when the number has more than 3 decimal point.

Example.
123.012 - multiply by 10
123.0123 - multiply by 100
123.01234 - miltiply by 1000

Column A is the result of the number being multiplied according to decimal point.
Column B contains the multiplier used in column A.

View 10 Replies View Related

Manually Find Difference In Days And Multiply By 24

Apr 25, 2011

1) First Problem:

Difference in hours is supposed to be 35 but it comes as 34.When i manually find the difference in days and multiply by 24 it comes as 34.999999999 and floor converts it as 34.. But when i execute the difference between dates alone and convert to 24 it shows as 35. So when i apply floor to this, it should be 35 instead of 34 right?

SQL> select (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss'))
3 from dual
4 ;
[code].....

1) Second Problem:

For the following query, difference of the dates in minutes is 2100.. so mod(2100,60) should give 0 but it's giving 60.. Separately if if put mod(2100,60) it is giving as 0 only.

SQL> SELECT MOD(((to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
2 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440),
3 60) AS duration,
4 (to_date('2011-05-03 09:00:00', 'yyyy-mm-dd hh24:mi:ss') -
5 to_date('2011-05-01 22:00:00', 'yyyy-mm-dd hh24:mi:ss')) * 1440 as diffinmin
[code]....

View 2 Replies View Related

Application Express :: Prefix Text Entered Into Text Area With Userid And Timestamp?

Sep 12, 2012

Have got basic form on a table and have a textarea which holds Notes added by user.

So Notes database field is updated on Save / Apply changes button being pressed.But would really like any text added / appended to the Notes field to be prefixed by userid and date / timestamp.

Is it possible via dynamic actions or Javascript to have any new text added / typed to be auto prefixed as per above.

Would only want the first key press in the filed to trigger the auto-prefix and if added text was deleted then the auto prefix to be deleted as well ?? If user doesn't press Save / Apply changes obviously want to leave existing Notes as is.

View 5 Replies View Related

Forms :: Is There Text Changed Trigger With Text Item Function

May 5, 2010

Is there text changed trigger with text item function like when_list_changed trigger of list item?

View 7 Replies View Related

Forms :: MS Word Rich Text Format In Text Item

Apr 17, 2012

Can I use rich text item on oracle form10G with some simple features like BOLD, UNDERLINE, ITALIC and if possible one more feature like spell check.

I Google my requirement, but mostly I found win word attachment. Further more if I can save this type of data in field then how can I print in report.

View 3 Replies View Related

Text :: Index Memory Parameter For Oracle Text Indexes

Sep 17, 2013

I am on Oracle 11.2.0.3 on Linux and have implemented Oracle Text.I created Oracle Text indexes with default setting. However in an oracle white paper I read that the default setting may not be right. Here is the excerpt from the white paper by Roger Ford:URL....(Part of this white paper below....)Index Memory.                                  

As mentioned above, cached $I entries are flushed to disk each time the indexing memory is exhausted. The default index memory at installation is a mere 12MB, which is very low. Users can specify up to 50MB at index creation time, but this is still pretty low. This would be done by a CREATE INDEX statement something like: CREATE INDEX myindex ON mytable(mycol) INDEXTYPE IS ctxsys.context PARAMETERS ('index memory 50M');  Allow index memory settings above 50MB, the CTXSYS user must first increase the value of the MAX_INDEX_MEMORY parameter, like this:  begin ctx_ adm. set_ parameter('max_index_memory', '500M'); end;  The setting for index memory should never be so high as to cause paging, as this will have a serious effect on indexing speed. On smaller dedicated systems, it is sometimes advantageous to temporarily decrease the amount of memory consumed by the Oracle SGA (for example by decreasing DB_CACHE_SIZE and/or SHARED_POOL_SIZE) during the index creation process.

Once the index has been created, the SGA size can be increased again to improve query performance." (End here from the white paper excerpt)My question is:

1) To apply this procedure (ctx_adm.set_parameter) required me to login as CTXSYS user. Is that right? or can it be avoided and be done from the application schema? This user CTXSYS is locked by default and I had to unlock it. Is that ok to do in production?

2) What is the value that I should use for the max_index_memory should it be 500 mb - my SGA is 2 GB in Dev/ QA and 3GB in production. Also in the index creation what is the value I should set for index memory parameter  - I had left that at default but how should I change now? Should it be 50MB as shown in example above?

3) The white paper also refer to rebuilding an index at some interval like once in a month:   ALTER INDEX DR$index_name$X REBUILD ONLINE; We are on Oracle 11g and the white paper was written in 2003.

View 5 Replies View Related

Forms :: Text Box To Accommodate Large External File Text

Dec 30, 2010

I have a requirement in one of my forms screen.I have a text box(large text area) which should display a help text file when i move my cursor on the topics displayed on the screen.know the code and the properties to be changed in the text box to accommodate large external file text.

View 1 Replies View Related

SQL & PL/SQL :: Converting HTML Format Text To Plain Text?

Dec 23, 2009

Is there any way to convert HTML format text to Plain Text ?

View 26 Replies View Related

Text :: Number Search Using Oracle Text

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

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







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