I wanted to display numbers as alphanumberics

Question

Suppose in table I have a number column as

I want to display those rows as

one
two
three
four

How can I do using SQL

I apologize for miscommunication, All the data present in one column that empid column, so I need display all the data in empid column as alphabets — siva krishna, Mar 14 '16 at 06:43

score 2 · Answer 1 · answered Mar 14 '16 at 06:40

2

You can use a technique from this blog which uses a hack with dates to get the text version of numeric fields. The blog post goes into much more detail, but in short, it converts the number to a Julian date which lets TO_CHAR use the format specifier sp (spelling out in text)

SELECT num, TO_CHAR(TO_DATE(num, 'J'), 'Jsp') num_as_text 
FROM myTable
ORDER BY num;

# num  num_as_text
# ----------------
# 1    One
# 2    Two
# 3    Three
# 4    Four

answered Mar 14 '16 at 06:40

Joachim Isaksson

176,943
25
281
294

Thanks for your answer Joachim Isaksson this is what I need – siva krishna Mar 14 '16 at 06:49

score 1 · Answer 2 · answered Mar 14 '16 at 06:41

1

You could use the j --> jsp technique to spell the number. It's been a FAQ.

j = julian. take the number and pretend it is a julian date, convert it into a date.
jsp = Take that date and spell the julian number it represents.

For example,

SQL> SELECT LEVEL,
  2         to_char(to_date(LEVEL,'j'), 'jsp') num_spell
  3  FROM dual
  4  CONNECT BY LEVEL <= 10;

     LEVEL NUM_SPELL
---------- ----------
         1 one
         2 two
         3 three
         4 four
         5 five
         6 six
         7 seven
         8 eight
         9 nine
        10 ten

10 rows selected.

SQL>

answered Mar 14 '16 at 06:41

Lalit Kumar B

47,486
13
97
124

Excellent Lalit, it shows the output exactly how I want, I lost a interview bcz of this question, Thanks to you. – siva krishna Mar 14 '16 at 06:48
@sivakrishna All the best for your future interviews :-) Please mark it as answered, would help others too! – Lalit Kumar B Mar 14 '16 at 06:49

score 0 · Answer 3 · answered Mar 14 '16 at 06:34

Assuming the schema is structured as you suggest like:

Table 
1     2     3     4
value value value value
value value value value
value value value value

You can use AS to rename the column in a query:

SELECT 1 AS one
       2 AS two
       3 AS three
       4 AS four
FROM table

I wanted to display numbers as alphanumberics

3 Answers3