SQL & PL/SQL :: How To Convert Varchar2 To Number

Jan 15, 2013

How to convert varchar2 to number data?

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Convert Varchar2 To Date Or Number

Jun 18, 2011

I created table Contains then following columns

cheq varchar2(50)
date_due varchar2(50)

and data entry in this columns

cheq 500,1500,5000 all values numbers in this columns

date_due 1-1-2012 , 15-9-2010 all values in this columns date

i want sum the column "cheq"

when used this code but it's not working

select sum(to_number(cheq))
from table_name but the code not working

second column "date_due"

i want search between to date i used this code but also not working

select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')

but not work

View 13 Replies View Related

SQL & PL/SQL :: How To Convert Varchar2 To CLOB

Mar 28, 2008

How to convert a varchar2 column to CLOB when there is a thousands of records in it.

View 15 Replies View Related

SQL & PL/SQL :: Convert VARCHAR2 To Date Data Type

Jun 13, 2012

I have problem to convert simply varchar to date data type.

For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08

However I try to do it I get always some errors.

select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual

View 7 Replies View Related

SQL & PL/SQL :: Conversion Varchar2 To Number

May 14, 2011

I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.

create table A(Col1 number);
Inert into A values(1);
Inert into A values(2);
Inert into A values(3);
Inert into A values(4);

Create table B(Col1 Varchar2(100));
Insert into b value ('1,2,3');

Select * from A where col1 in (select col1 from b)
Error: Invalid Number

Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.

View 7 Replies View Related

PL/SQL :: Invalid Number With VARCHAR2

Apr 3, 2013

I keep getting this error when I run my update statement. Here is what the coding looks like. I'm running Oracle 11g.

CREATE TABLE A (
SUPP_CD_EIM VARCHAR2 (20),
SUPP_CD VARCHAR2(20),
DSN_FAC_CD VARCHAR2(5));
[code]......

I want the output to look like

A1_1234
A2_2345
A3_3456

View 2 Replies View Related

Conversion Number(19) To Varchar2(19)

Sep 18, 2009

When I try to convert numeric values � number(19) p.s 111111111111111111, the to_char function returns �1111111111111110000� because the to_char functions doesn�t support precision bigger than 15.

Is there any way to solve it?

View 3 Replies View Related

PL/SQL :: Remove Spaces Between Two Number From A Varchar2?

Oct 18, 2012

I have a question respect to remove spaces from a varchar2.

The varchar2 is '7987451 1234567' and i need that string like '7987451 1234567', because the field has a length of 15.

i try with this, but does nothing

Select TRIM('7987451 1234567') from dual

Other possibilities are find several spaces and try to replace with only one, but would a heavy work (may be with sentences Loop).

This problem is now in our loading processes. Exists some function to replace spaces between numbers of varchar2?

View 2 Replies View Related

SQL & PL/SQL :: Check Constraint To Disallow Number In Varchar2 Column

Apr 21, 2010

I have table customer which contains a column CUSTOMER_FIRST_NAME

CUSTOMER_FIRST_NAME VARCHAR2(50)

What will be sql statement to add a constraint on the CUSTOMER_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers ?

View 34 Replies View Related

SQL & PL/SQL :: Composite Index Having Varchar2 Number And CLOB Column

Feb 25, 2012

I need to create a composite unique index on varchar2, number and CLOB column. I haven't used such index before that have the CLOB column indexing. I found the below link related to CLOB indexing...

[URL]......

Links from where I can get related info. Also I would like to know the impact of such index on performance. I have to store and process around 50 million records in such a way, will it be beneficial to use this index?

View 11 Replies View Related

PL/SQL :: Automatic Rounding Off Number Datatype While Converted To VARCHAR2

May 29, 2013

I am facing issue related to Number data while it is being converted to Varchar2, it is automatically getting rounded off after 32 decimal place.My database version is "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

1) create table test18 ( col1 varchar2(10), val Number);
create table succeeded.

2) insert into test18 values ('First', -347026.6408499652467480885711448714129679); -- After decimal 34 digits
1 rows inserted

insert into test18 values ('Second', -347026.64084996524674808857114487141296); -- After decimal 32 digits
1 rows inserted

3) select * from test18;

COL1 VAL
---------- ----------------------
First -347026.6408499652467480885711448714129679
Second -347026.64084996524674808857114487141296

4) As per the requirement, all the columns would need to be concatenated as a single string along with comma delimiter

select col1 || ',' || val as record_string
from test18;

RECORD_STRING
---------------------------------------------------
First,-347026.64084996524674808857114487141297
Second,-347026.64084996524674808857114487141296

"First" string got rounded off to 97 (last 2 digits) instead of 9679 but for "Second" record it holds the actual value.Only thing which I could figure out while the number is getting type casted to String, it is getting rounded off to 32 decimal place.throw off some light on it and provide the solution how the original record can be kept intact without rounding off.

Expected Output_
RECORD_STRING
---------------------------------------------------
First,-347026.6408499652467480885711448714129679
Second,-347026.64084996524674808857114487141296

View 10 Replies View Related

Table Partitioning - Range On Datatype Number (21, 7) And Varchar2

Nov 15, 2013

Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON  or PROFILE  either one table which is having below

structure :   Name                Null?    Type
-------------------- -------- --------------------------------
MANDT                NOT NULL VARCHAR2(9) MR_ID                NOT NULL VARCHAR2(60) PROFILE              NOT NULL VARCHAR2(54) REGISTER_ID          NOT NULL VARCHAR2(30) INTERVAL_DATE        NOT NULL VARCHAR2(24) AGGR_CONSUMPTION     NOT NULL NUMBER(21,6) MDM_VERS_NO          NOT NULL VARCHAR2(9) MDP_UPDATE_DATE      NOT NULL VARCHAR2(24) MDP_UPDATE_TIME      NOT NULL VARCHAR2(18) NMI_CONFIG           NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG      NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID     NOT NULL VARCHAR2(6) NSRD                 NOT NULL VARCHAR2

[Code]....

 As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible.  How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal

View 0 Replies View Related

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

SQL & PL/SQL :: How To Convert RAW To Number

Sep 27, 2013

can we convert RAW to number in sql.because i have to bitand of one raw and number variable.

View 3 Replies View Related

PL/SQL :: Convert Number?

Jun 14, 2013

would need to convert a number in this way:10.1 ---> 10101.90 ----> 190  How can i?

View 1 Replies View Related

SQL & PL/SQL :: Convert Char To Number

Mar 16, 2011

I've got a table with varchar records and I need to separate numerical value and convert into the number datatype.

create table tmp_mape

(remark varchar2(100) )

insert into tmp_mape values ('Dobitie zdarma 3,32EUR 0910105067 02/02')
insert into tmp_mape values ( 'Dobitie pravidelné 9,00EUR' )

I just need to get values 3.32 and 9 from that example into the new column.

The select below returns ORA-01722: invalid number

how to solve it?

select remark,
to_number( to_number(REGEXP_SUBSTR (remark, '[[:digit:]]+', 1) ) ||','|| to_number(substr(REGEXP_SUBSTR (remark, ',[^EUR]+', 1) ,2) ) )
from tmp_mape

View 7 Replies View Related

SQL & PL/SQL :: How To Convert Number To Date

Oct 28, 2011

how can convert this number 00001021992 to this format

1-02-1992

i used thie query but no result

select substr(to_date('00001021992','dd-mm-yyyy'),(6,6)) from dual;

View 6 Replies View Related

SQL & PL/SQL :: Convert Character Into Number

Jul 30, 2010

the basic salary has data type character.when i write

select basic_salary from table_name

it shows output.but when i need annul for that basic salary(basic_salary*12) it shows error. invalid number.

View 6 Replies View Related

PL/SQL :: Convert Number To Hours

Jan 1, 2013

I want to convert below MS-SQL query in oracle 10g.

for eg:

Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable

Output:

9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes

View 8 Replies View Related

PL/SQL :: Convert Number Into Time?

Mar 8, 2013

I need a query that convert number into time.

View 5 Replies View Related

PL/SQL :: Convert To Number Without Decimals

Sep 10, 2012

I have a value that I need in the number format, but without decimals. Is this possible?

TO_NUMBER(SY_KEY1,'9999999') AS SY_KEY1The result I get back is:

1114225.0So, I need a result of 1114225 and in the number format.

View 2 Replies View Related

VARCHAR2 (100) Changes Column To VARCHAR2 (100 Byte)

Oct 24, 2007

i am fairly new in the oracle arena, but what would cause a statement such as

ALTER TABLE TEST_TABLE MODIFY text_field1 varchar2(100) DEFAULT 'testval' NULL

to change a column's type from VARCHAR2(100) to VARCHAR2(100 byte)? i found a few mentions of the 100 byte concept online but nothing that jumped out at me.

View 2 Replies View Related

SQL & PL/SQL :: How To Convert Decimal Number To A Character

Aug 14, 2013

I want to convert decimal number 44 to character(" ' " i.e single quotation);

I have already tried to_char function but it's not working.

View 2 Replies View Related

PL/SQL :: How To Convert Number (15 Characters) To Date

Jun 23, 2013

One table ACCOUNT_T.CREATED_T is number (38). It is mapped to /ACCOUNT's PIN_FLD_CREATED_T, which is timestamp. ACCOUNT_T table has one record, CREATED_T = 1362063600. This field is displayed at the GUI (Customer Center) as Feb 28, 2013 So my question, how to convert the number value (1362063600) to a date value (Feb 28, 2013)?

View 4 Replies View Related

PL/SQL :: Convert A Number To Years And Months

Jul 30, 2012

I would like to convert a number to years & months.

e.g. 119 = 9 years and 11 months

This would be displayed as 9.11

Is there an oracle function or sql that can calculate this value,

View 8 Replies View Related

Forms :: How To Convert Exponent Into Pure Number

Nov 22, 2006

I have to convert the following

3.3767E+14
4.40453E+15
4.40453E+15

into

337670137917014.00
4404530588226230.00
4404530588226230.00

Any function or formula for this one?

View 35 Replies View Related

SQL & PL/SQL :: Convert Number To Specific Format Using Query

Apr 14, 2011

I want to convert a number to this format using below query but i'm getting error. how to correct the below query.

SELECT TO_CHAR(12345678, '99G9999D99') FROM dual;

error:-###########

View 2 Replies View Related

SQL & PL/SQL :: Convert Year / Quarter Number To Date Format

Oct 5, 2010

I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.

View 5 Replies View Related

Reports & Discoverer :: Convert Exponential Number Into Pure While Writing On Excel Using Builder

May 16, 2013

How to convert exponential number into pure number while writing on excel using report builder.Excel only takes 15 digit as pure number and remaining number it converts into zero or you can say in exponential form.

View 4 Replies View Related

Reports & Discoverer :: Convert Number From Arabic To English In Oracle Report Builder

Aug 5, 2011

how can convert number from Arabic to English in oracle report builder

View 2 Replies View Related







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