select numeric values from a varchar column
For Example:
select * from t1 ;
ID
----------
00300
ABCXY
04230
xyzab
i need to fetch only numeric values from column id
My output should be
00300
04230
I have a table and data like mentioned below.
create table emp( ename varchar2(20));
insert into emp values ('122');
insert into emp values ('abc');
insert into emp values ('0.2');
insert into emp values ('0-5');
insert into emp values ('25-30');
SQL> Select * from emp;
| ENAME |
-------------------
| 122 |
| abc |
| 0.2 |
| 0-5 |
| 25-30 |
I am running the code
SQL>select regexp_substr(ename , '^[[:digit:]]+.[[:digit:]]+$|^[[:digit:]]+$')
from emp;
AFTER RUNNING I AM GETTING THIS
| REGEXP_SUBSTR(ENAME,'^[[:DIGIT:]]+.[[:DIGIT:]]+$|^[[:DIGIT:]]+$') |
---------------------------------------------------------------------
| 122 |
| (null) |
| 0.2 |
| 0-5 |
| 25-30 |
| (null) |
Why it's not excluding '0-5' and '25-30', how I should write code to exclude this and Is there is any function in oracle to check for numeric in column and print.
I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?
bubbagumpshrimp
"ORA-01722: invalid number"
SELECT x.y
from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y
from PS_NAMES
where emplid > '000000000' and emplid < '999999999') x
where rownum = 1;
Is there any way in PL/SQL to select the values from all columns of a table record into an array?
For example:
C1|C2|C3
0 |1 |2
v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2
or
v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2
But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;
I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:
idCoupon Type
123Amount
123Percent
456Amount
789Percent
I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:
idCoupon Type
123Amount
123Percent
I have following tables with data as under:
table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9
I need to generate output as :
column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null
Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?
I need to write a function which will take table name as input and should return all the columns separated by coma (,).
For example I have a table product as
PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001
My function should return the output as
100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001
Is there any inbuilt function available?
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
I have a set of data within a table with a column called telephone_numbers. What I have noticed is that for some reason I have data in there that is not numerical values only i.e. LLSSUU. Is it possible to delete all non numerical values from this column?
The problem is I have over 1000 fields to go through and was wondering if there is a query that i can write.
how to convert existing table with varchar column to a clob
View 1 Replies View RelatedIn my table ,data type of one among 10 columns is defined as varchar2(10).I need to check that column should accept only numeric value(0 to 99) or alphabetic value(a to z or A to Z) .It should not accept Alpha-numeric values.I tried like this
select c3 from demotab where to_number(c3) not between ascii('a') and ascii('z') ;
but I got error like 'Invalid Number'.how to implement this thro sql query.
I would like to know if using varchar parameter in sql queries with number column can result in performance degrade.
Ex: Procedure testa ( myparam varchar) is
begin
select col1 into var1 from table where colno = myparam;
end;
Here col no is a number column and myparam is varchar. I feel its better to change the parameter to number.
I have an sqlldr process running loading data into my database. I have created a trigger to run before inserts on each row to start gathering summary data from the basic underlying data. The trigger compiles ok and the procedures the trigger is calling compile ok, but when the sqlldr process runs I get errors in the log files.
Here is the sqlldr control file:
LOAD data
APPEND INTO TABLE cdr.day_tables
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
RecordCode
,CdrStart DATE 'YYYY DDD SSSSS'
[code].......,
Next is my trigger
create or replace TRIGGER BNUMBER_SUMMARY_INS
BEFORE INSERT ON DAY_TABLES
FOR EACH ROW
DECLARE
[code]......
Next are the procedures that are called by the trigger:
create or replace PROCEDURE BNUMBER_SUMMARY
( BNUMBER IN VARCHAR2
, CALLDATE IN DATE
, CALLDURATION IN NUMBER
) AS
record_found NUMBER;
BEGIN
[code].......
The error messages I am getting are:
Record 1: Rejected - Error on table CDR.DAY_TABLES, column CDREND.
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "CDR.BNUMBER_SUMMARY_INS", line 6
ORA-04088: error during execution of trigger 'CDR.BNUMBER_SUMMARY_INS'
I need to find out what field it is complaining about, especially since I am not even using the cdrend field from the input record?
select ORDER_NUMBER from OE_ORDER_HEADERS_ALL
WHERE ordered_date=to_char(to_date(substr(ORDERED_DATE,1,10),'YYYY/MM/DD'),'DD-MON-YYYY');
Error:-ORA-01858: a non-numeric character was found where a numeric was expected
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
I get the error message mentioned in the subject with this SELECT-statement
....where (t.cfonte=14 and t.data_ultima_modifica between sysdate -4000/(24*60*60) and sysdate ) or (t.data_ultima_modifica > to_date('%TIMESTAMP%','ddmmyyhh24miss'))]]>
If I substitute %TIMESTAMP% with 310507143709 then it works
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedFrom two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedOn my APEX page i have region which has sql query as source and it displays as HTML table the query result to the user.
I want to display addinonal column with a hyperlink inside, and that hyperlink would have CGI/URL-parameters which contains the other values of the HTML row.
So, let's say my APEX region queryes columns as "select c1, c2, c3, c4 ..." and displays out values "V1, V2, V3, V4" then i want to have addional output column with such hyperlink:
a href="f?p=100:7:13467554876288::NO::c1,c2,c3,c4:v1,v2,v3,v4">My link column with CGI-parameters</aHow can i create such hyperlink?
The overall idea is that the link would forward to a page which loads those values "v1,v2,v3,v4" into form fields and user can proceed from there.
---
"Application Express 4.2.1.00.08"
We are getting an error : a non numeric was found where a numeric was expected sometimes when this statement is executed:
INSERT INTO training select * from temp_training where class_id='xyz';
all columns and their datatypes are the same in both the tables
however if i replace the * with the column names as shown below it seems to work fine without giving an error
insert into training (a,b,c) select a,b,c from temp_training where class_id='xyz'
wanted to understand the subtle difference between the 2 statements
I want to pass multiple column values of a row in an interactive report page to hidden items in another page through column link. And I did it successfully. However, I found I need to pass more than 3 columns of a row in this report, while a column link only permits me to pass 3 column value at most. Is there anyway that I can pass more column values to hidden items in another page?
View 3 Replies View RelatedCREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
I have a INSERT query which is happening with a SELECT query.
===================================================
INSERT INTO tbl_fact_effort_lvl_data ( ............... )
SELECT ria.report_id,report_status: :,
((SELECT lov_num_val
FROM tbl_reference_data
WHERE lov_type = 'FREQUENCY' ) * (SELECT SUM(pph_task)
FROM tbl_ri_process
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id
FROM tbl_fact tf LEFT JOIN ......... ;
====================================================
So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.
i have a stored proc where i am selecting a value into a variable like so:
SELECT FUNCTION
INTO V_FUNCTION
FROM FUNCTION_TABLE
WHERE FUNCTION = P_INPUT;
Now, my problem lies in where there is no value returned (oracle will throw an error).
there are some data in the table que_history (seqnbr is the key), e.g.
SEQNBR DN SL_TIME
20070927003668 (024)2272 AD182040 2007-9-27 15:15:00
20070928001343 (024)2272 AD182040 2007-9-28 9:55:14
20070928001624 (024)2272 AD182040 2007-9-28 10:30:06
20070928000910 (024)25672 AD000002 2007-9-28 9:06:59
20070928001288 (024)25672 AD000002 2007-9-28 9:49:13
20070923003834 (024)2585 AD210076 2007-9-23 17:15:13
20070923003890 (024)2585 AD210076 2007-9-23 17:23:54
20071001001593 (024)2589 AD000018 2007-10-1 11:54:39
20071003002814 (024)2589 AD000018 2007-10-3 16:53:52
20070923003320 (024)8831 AD000110 2007-9-23 15:24:39
I wanted to use this SQL to get the records ( dn is the same and the sl_time's interval is 600minutes) .
select A.* from que_history A,que_history B
where A.dn=B.dn and A.seqnbr<>B.seqnbr
and (A.sl_time-B.sl_time)*24*60 between -600 and 600
order by A.dn;
but the result is not the right.
I have an LOV on my form which holds a list of course units for a student to select and insert. However some units on the LOV must be COMPULSORY(not optional).... so i was wondering is there a way to have these auto selected from the LOV?
View 4 Replies View RelatedIs there a way to loop through a list of literal values.
For instance
create table car(
name varchar2(11),
passengers int,
price int
);
insert into car values ('fiat',1,1000);
insert into car values ('bmw',2,2500)
insert into car values ('ford',2,1500)
insert into car values ('ferrari',4,5000)
select
max(price)
from car
where passengers=1
How can i in a single query do this for where passengers = 1
then passengers = 2
then passengers = 3 etc
where i have a list of possible values for passengers.
Just to update I realise this can be done with
select
name,
max(price)
from car
where passengers in (1,2,3)
group by name
but in just wanted to know if there is a way of iterating through a literal list in tsql