Forum Stats

  • 3,855,354 Users
  • 2,264,499 Discussions
  • 7,905,978 Comments

Discussions

Categories

datatype for number and character

582889
582889 Member Posts: 1,960
edited Sep 9, 2008 10:19AM in SQL & PL/SQL
Hi
I want a field to be consist of both numeric and character values.
Is there any datatype like this in oracle?
Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,363 Red Diamond
    Answer ✓
    ;)
    SQL> create table vehicles (reg varchar2(10));

Table created.

SQL> insert into vehicles (reg) values ('VIC123');

1 row created.

SQL> insert into vehicles (reg) values ('ABC4566');

1 row created.

SQL> select * from vehicles;

REG
----------
VIC123
ABC4566

SQL>
    I assume your 1300 odd other posts are not in any of the SQL, PL/SQL or developer type forums then as I would expect someone with that many posts to know that they can store such things in a VARCHAR column?
    · Share on TwitterShare on Facebook
«1

Answers

  • NicloeiW
    NicloeiW Member Posts: 1,811 
    Data type "anydata":

    
If you REALLY REALLY have to store numbers and varchars in the same column, then you can use the SYS.ANYDATA datatype to properly control it.
    
That means you get a built-in (and therefore supported) function to determine if the value is numeric, which can be included in a CASE or DECODE so that it is guaranteed that only numeric values will be exposed.

    
SQL> create table test (col_a anydata);

    
Table created.

    
SQL> insert into test values (anydata.convertnumber(10));

    
1 row created.

    
SQL> insert into test values (anydata.convertvarchar2('abc'));

    
1 row created.

    
SQL> select anydata.gettypename(col_a) from test;

    
ANYDATA.GETTYPENAME(COL_A)
    
---------------------------------------------------------

    
SYS.NUMBER
    
SYS.VARCHAR2

    
SQL> select case when anydata.gettypename(col_a) = 'SYS.NUMBER' then
    
  2      anydata.accessnumber(col_a) end col_a_val, rownum
    
  3  from test;

    
 COL_A_VAL     ROWNUM
    
---------- ----------
    
        10          1
    
                    2
    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,890 Red Diamond
    I want a field to be consist of both numeric and character values.
    Why? This does not make sense from a relational design perspective. Nor does it make any sense from a RDBMS perspective ito indexing, foreign keys, selecting/updating/inserting, performance, etc.

    You need to supply very valid and sound reasoning for wanting to do this.. and I for one will like to see what these are as I cannot think of a single reason myself...
    · Share on TwitterShare on Facebook
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    Hello Billy,

    we have one application that uses anydata and stores it in a table. It is similar to an Excel sheet and in fact in the end an Excel file is generated from it.
    Something like APEX but only that a filed can also contain formulas, formatting, links etc.

    Regards
    Marcus
    · Share on TwitterShare on Facebook
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,890 Red Diamond
    If it is like an Excel cell, then it should simple be a string - as it requires parsing at run-time to determine what that cell is going to contain. E.g. a sum of other cells, result of a formula, number, text, date, etc.

    As soon as you mix different data types in the same column, you decrease the column's flexibility drastically. Software is more robust and performs faster when dealing with known's than unknown's as the latter requires interpretation at run-time.
    · Share on TwitterShare on Facebook
  • 582889
    582889 Member Posts: 1,960
    edited Sep 9, 2008 7:55AM
    well,
    I have column name "vehicle_registeration"

    The data of this column should be both numeric and character like:
    VIC123
    QNL456
    ...
    ...

    How can I do that?
    · Share on TwitterShare on Facebook
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    Hello Richardinho,
    from your original question I (and it seems Billy too) thought that the column should be able to hold numbers as numbers and characters as characters. If it only has to contain numbers as characters you can simply use VARCHAR2. Then VIC123 is just 6 characters.

    Regards
    Marcus
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,363 Red Diamond
    Answer ✓
    ;)
    SQL> create table vehicles (reg varchar2(10));

Table created.

SQL> insert into vehicles (reg) values ('VIC123');

1 row created.

SQL> insert into vehicles (reg) values ('ABC4566');

1 row created.

SQL> select * from vehicles;

REG
----------
VIC123
ABC4566

SQL>
    I assume your 1300 odd other posts are not in any of the SQL, PL/SQL or developer type forums then as I would expect someone with that many posts to know that they can store such things in a VARCHAR column?
    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,363 Red Diamond
    edited Sep 9, 2008 9:22AM
    + edit: duplicate caused by forum error +

    Edited by: BluShadow on Sep 9, 2008 2:21 PM
    · Share on TwitterShare on Facebook
  • 582889
    582889 Member Posts: 1,960
    ok sorry for any inconvinience
    · Share on TwitterShare on Facebook
  • Marwim
    Marwim Member Posts: 3,653 Gold Trophy
    Hello Billy,
    Billy Verreynne wrote:as it requires parsing at run-time to determine what that cell is going to contain
    Thats the problem why we decided to "fix" the datatype. If you open a CSV or an Excel file with unformatted cells then Excel begins to guess which kind of data the cell holds and you end with 5.09 shown as "Mai 09" and many other nonsense.
    Billy Verreynne wrote:Software is more robust and performs faster when dealing with known's than unknown's as the latter requires interpretation at run-time.
    But that is exactly what we do. When the user enters data he knows the type of the data (number, date text). Interpretation later when creating the file or opening the file with excel inevitably corrupts the data.

    Regards
    Marcus
    · Share on TwitterShare on Facebook
This discussion has been closed.