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
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 make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
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.
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
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
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 ;
In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used export LANG="universal.UTF-8" in order to make it English. which works fine.
But my question is how to retain this special character intact as, it is in DB?
So in UNIX script export LANG="universal.UTF-8" sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d" set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000 spool /path/out.txt Select name from tablename where is=12; spool off; exit ENDSQL
Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.
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
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 :
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
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).
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.
/*---*/ 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.
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.
