CODEWITHSUNDEEP
sqloracle-databaseplsql
R.S
R.S

Reputation: 219

how to check a field is char or number?

I have a NVARCHAR field in my table. (using Oracle 11g)

I want check it has char data or number data.

help me to write a select query. (using PL-SQL)

I think I can write it with case, but I can't implement that. (I have no method for checking isChar and isNumber)

select
       case <myField>
           when <isChar> then
               <this is a char data>
           when <IsNum> then
               <this is number data>
       End
from <myTable>

Upvotes: 3

Views: 31017

Answers (2)

peterm
peterm

Reputation: 92785

Using REGEXP_LIKE

SELECT t.*,
       CASE 
         WHEN REGEXP_LIKE(column1, '^[0-9]+$') THEN 'Numeric' 
         ELSE 'Char' 
       END data_type 
  FROM table1 t;

And without it

SELECT t.*,
       CASE 
         WHEN LENGTH(TRIM(TRANSLATE (column1, '0123456789',' ')))  IS NULL THEN 'Numeric' 
         ELSE 'Char' 
       END data_type 
  FROM table1 t;

Here is SQLFiddle demo

Both versions can be adjusted to accommodate period, plus and minus signs if needed

UPDATE As @tbone correctly commented these queries don't take into account NULLs and return 'Char' and 'Numeric' as data_type respectively when a value of column1 is NULL.

There are at least two options how to deal with it:

First Simply filter out rows with NULL values if we are not interested in them

...
WHERE column1 IS NOT NULL

Second Introduce NULL (obviously it can be 'N/A' or something else) data type in CASE

SELECT t.*,
       CASE 
       WHEN column1 IS NULL THEN NULL
       ELSE CASE 
         WHEN REGEXP_LIKE(column1, '^[0-9]+$') THEN 'Numeric' 
         ELSE 'Char'
         END
       END data_type 
  FROM table1 t

Here is updated SQLFiddle demo

Upvotes: 5

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this SQL. I have assumed in case statement there is character data. 

 select case <myField>
        when (ascii(char)>=65 and ascii(char)<=90) OR (ascii(char)>=97 and ascii(char)<=122)  
        then
                   <this is a char data>
        when ascii(char)>=48 and ascii(char)<=57 
        then
                   <this is number data>
        End
 from <myTable>

Hope this helps.

Upvotes: 1

Related Questions