Menu
I have 5 character string which can have numbers, decimal points, alphabets and spaces. I wanted to convert this string to a number (integer) if all the characters in string are numbers. i.e.
Thanks in advance.
693
TO_NUMBER converts a string to a number of data type NUMERIC. TO_CHAR performs the reverse operation; it converts a number to a string. CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER.
The TO_NUMBER function converts its argument to a DECIMAL data type. The argument can be the character string representation of a number or a numeric expression.
The TO_CHAR function converts DATETIME or DATE values to character string values. The TO_CHAR function evaluates a DATETIME value according to the date-formatting directive that you specify and returns an NVARCHAR value.
TO_CHAR (datetime) converts a datetime or interval value of DATE , TIMESTAMP , TIMESTAMP WITH TIME ZONE , or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt .
Use To_Number Function in PL/SQL to convert a string into number, see below for example.
to_number('1210.73', '9999.99') would return the number 1210.73
to_number('546', '999') would return the number 546
to_number('23', '99') would return the number 23
EDIT:
In PL/SQL you can check whether a string consists of numeric characters or not by using LENGTH, TRIM and TRANSLATE functions.
LENGTH(TRIM(TRANSLATE(string1, ' +-.0123456789', ' ')))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
