sql – Is it possible to convert strings like John and Jane to numeric values but keep them readable as John and Jane
Use base 62 instead of decimal. With base 62 you can use all your letters (lower case and upper case) as numbers.
0 - 0 ... 9 - 9 a - 10 ... z - 35 A - 36 ... Z - 62
It really sounds like youre going about this wrong. You dont say enough about your application, but youre trying to union together a text and numeric column. Im imagining something like this:
SELECT t1.my_numeric_column FROM mytable1 t1 UNION ALL SELECT t2.my_varchar2_column FROM mytable2 t2
And as you say, this doesnt work. What you should be doing is this:
SELECT TO_CHAR( t1.my_numeric_column ) FROM mytable1 t1 UNION ALL SELECT t2.my_varchar2_column FROM mytable2 t2
You say this is not ideal because then my output is text instead of numeric, but if youre displaying this text somewhere (and you dont say where or how), then your output is text, not numeric.
Either that or the problem that youre trying to solve isnt well defined here.