0

I am new to Oracle SQL and I wasn't able to find correct answer to it.

For example I'm creating new table.

CREATE TABLE "DATASTORE"."NEWTABLE_A" 
   ("BUSINESS_NAME" VARCHAR2(100 BYTE), 
    "TYPE"          VARCHAR2(100 BYTE))

How do I identify the length of strings? Should have to find the longest word in column and calculate it in BYTES?

Thanks

Arsen K
  • 1
  • 1

1 Answers1

1

Set it to be "large enough". Quite often it is pure estimation.

In your example, if "types" are - for example - A, CX and DDF, then setting that column to 100 bytes is far too much. But, if it can be anything between A and 7-words string, then 100 bytes might be just fine.

Certain columns are simpler to estimate or know for sure (such as "date of birth" - it'll be DATE datatype; or 2-gender sex - you'd use VARCHAR(1)). Others are rather difficult, so - give your best and estimate as close as you can. Even if you fail, you can alter table and modify column length. Though, that might cause problems in programs you based on that table.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • VARCHAR2(1), surely. – William Robertson Sep 07 '18 at 22:11
  • @William, I said CHAR intentionally. It is M or F and nothing else. I don't use it nor would I recommend it, but - for purposes of this discussion, I thought that such a statement would make a point. On the other hand, yes - your comment makes sense as well. – Littlefoot Sep 08 '18 at 05:12
  • Even so, `varchar2` should be the standard string type. I wouldn't want to encourage the OP to start creating `char` columns. [(My views on `char`.)](https://stackoverflow.com/a/42165653/230471) – William Robertson Sep 08 '18 at 08:01
  • As I agree with you anyway, I'm going to edit my message and change CHAR to VARCHAR2, @William. – Littlefoot Sep 08 '18 at 13:57
  • What if in column TYPE we have values Financial, Commercial, Internet, Commodity etc... mo than 1000 different values? – Arsen K Sep 17 '18 at 17:57
  • Nothing; what difference does it make? 1000 values is, in Oracle world, *tiny*. Column datatype would be VARCHAR2(20) (or something like that; large enough to accept the longest value). Though, consider creating a separate table for types (with their ID as a primary key and NAME), and reference that table from other table(s) via foreign key constraints (i.e referential integrity). – Littlefoot Sep 17 '18 at 20:03