CODEWITHSUNDEEP
oracle-database
Charif DZ
Charif DZ

Reputation: 14721

Oracle change any string to a number

I'm having this problem we have this database witch IDS are stored in varchar2 type this ids contains Letters.

Is there any solution to convert a string to a number no matter what the value if this string.

for example there is : SELCT ASCII('t') FROM DUAL; result : 116.

but ASCII accept only one CHAR Hope you get the idea. sorry for my english

Upvotes: 1

Views: 3626

Answers (2)

user5683823
user5683823

Reputation:

EDIT: After discussing further with the OP, it turns out he needed a function (in the mathematical sense) from short strings to integers. Such a function is ORA_HASH. The OP decided that ORA_HASH is likely what is needed for his project.

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions112.htm#SQLRF06313

The solution below is kept for historical perspective.

You could use the analytic function DENSE_RANK to assign numbers to strings.

For example:

with
     employees ( id, first_name, last_name ) as (
       select 'ABC', 'Jane', 'Smith' from dual union all
       select 'ABD', 'Jane', 'Dryer' from dual union all
       select 'XYZ', 'Mike', 'Lopez' from dual
     )
--  End of simulated inputs (for testing purposes only).
--  Solution (SQL query) begins below this line.
select id, dense_rank() over (order by id) as num_id, first_name, last_name
from   employees
;

ID   NUM_ID  FIRST_NAME  LAST_NAME
---  ------  ----------  ---------
ABC       1  Jane        Smith
ABD       2  Jane        Dryer
XYZ       3  Mike        Lopez

Upvotes: 1

hvojdani
hvojdani

Reputation: 470

use oracle translate method to replace A-Z or a-z characters with numbers. then use to_number to get number from it.

select translate('A1B2C3', 'ABC', '456') from dual;    --result '415263'

select to_number(translate('A1B2C3', 'ABC', '456')) from dual;  --result 415263

translate function documentation

The Oracle/PLSQL TRANSLATE function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

Upvotes: 2

Related Questions