| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Convert character string to number
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C0867E.D346AEC0
Content-Type: text/plain;
charset="iso-8859-1"
see coments at end of e-mail.
> -----Original Message-----
> From: Helmut Daiminger [mailto:hdaiminger_at_vivonet.com]
> Sent: mercredi, 24. janvier 2001 16:41
>
> I want to read a column (varchar2) from a table and convert
> the contents
> into numbers if the string consists of numbers only. If the
> string contains
> characters, I don't want to convert it.
>
> Example for data in varchar2 colum:
>
> row 1: 12345
> row 2: text
> row 3: 123dfe
> row 4: 9876432
>
> I can easily conver row 1 and 4 using the to_number function,
> right? But how
> can I tell Oracle to skip rows 2 and 3 since the character field also
> contains characters not just numbers?
You could try this:
SQL> select v from x ;
V
SQL> select decode (translate (v, '~1234567890', '~'), null, to_number (v),
null)
2 from x ;
DECODE(TRANSLATE(V,'~1234567890','~'),NULL,TO_NUMBER(V),NULL)
12345
NULL VALUE
9876432
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
------_=_NextPart_001_01C0867E.D346AEC0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3Diso-8859-1">
<TITLE>RE: Convert character string to number</TITLE> </HEAD> <BODY>
<P><FONT SIZE=3D2>see coments at end of e-mail.</FONT> </P>
<P><FONT SIZE=3D2>> -----Original Message-----</FONT> <BR><FONT SIZE=3D2>> From: Helmut Daiminger [<A = HREF=3D"mailto:hdaiminger_at_vivonet.com">mailto:hdaiminger_at_vivonet.com</A>= ]</FONT>
<BR><FONT SIZE=3D2>> Sent: mercredi, 24. janvier 2001 16:41</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I want to read a column (varchar2) from a table = and convert </FONT>
<BR><FONT SIZE=3D2>> string contains</FONT> <BR><FONT SIZE=3D2>> characters, I don't want to convert it.</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> Example for data in varchar2 colum:</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> row 1: 12345</FONT> <BR><FONT SIZE=3D2>> row 2: text</FONT> <BR><FONT SIZE=3D2>> row 3: 123dfe</FONT> <BR><FONT SIZE=3D2>> row 4: 9876432</FONT> <BR><FONT SIZE=3D2>> </FONT> <BR><FONT SIZE=3D2>> I can easily conver row 1 and 4 using the =to_number function, </FONT>
<P><FONT SIZE=3D2>You could try this:</FONT> </P>
<P><FONT SIZE=3D2>SQL> select v from x ;</FONT> </P>
<P><FONT SIZE=3D2>V</FONT> <BR><FONT SIZE=3D2>----------</FONT> <BR><FONT SIZE=3D2>12345</FONT> <BR><FONT SIZE=3D2>text</FONT> <BR><FONT SIZE=3D2>123dfe</FONT> <BR><FONT SIZE=3D2>9876432</FONT> </P>
<P><FONT SIZE=3D2>SQL> select decode (translate (v, = '~1234567890', '~'), null, to_number (v), null)</FONT> <BR><FONT SIZE=3D2> 2 from x ;</FONT> </P>
<P><FONT =
SIZE=3D2>DECODE(TRANSLATE(V,'~1234567890','~'),NULL,TO_NUMBER(V),NULL)</=
FONT>
<BR><FONT =
SIZE=3D2>-------------------------------------------------------------</=FONT>
SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; 12345</FONT> <BR><FONT SIZE=3D2>NULL VALUE</FONT>
SIZE=3D2> &nb= sp; &nb= sp; &nb= sp; &nb= sp; 9876432</FONT></P>
<P><FONT SIZE=3D2>------</FONT>
<BR><FONT SIZE=3D2>any ignorant comments made are the sole =
responsibility of J. R. Kilchoer and should not reflect adversely upon =
my employer.</FONT></P>
<P><FONT SIZE=3D2> </FONT> <BR><FONT SIZE=3D2>Jacques R. Kilchoer</FONT> <BR><FONT SIZE=3D2>(949) 754-8816</FONT> <BR><FONT SIZE=3D2>Quest Software, Inc.</FONT> <BR><FONT SIZE=3D2>8001 Irvine Center Drive</FONT> <BR><FONT SIZE=3D2>Irvine, California 92618</FONT> <BR><FONT SIZE=3D2>U.S.A.</FONT> <BR><FONT SIZE=3D2><A HREF=3D"http://www.quest.com" =Received on Wed Jan 24 2001 - 21:28:03 CST
 |
 |