Home » SQL & PL/SQL » SQL & PL/SQL » upper case in create table
upper case in create table [message #155920] Thu, 19 January 2006 12:20 Go to next message
jchiu183
Messages: 16
Registered: September 2005
Junior Member
How can I use upper case in create table to make sure the users enter in upper cases?


CREATE TABLE TT(
NUM NUMBER NOT NULL PRIMARY KEY
,NAME VARCHAR2(60) NOT NULL
,TEAM VARCHAR2(12) NOT NULL
,AREA VARCHAR2(10) NOT NULL);


------------------------------------------------------------------- I need something like this but I got error as follow sql:

CREATE TABLE TT(
NUM NUMBER NOT NULL PRIMARY KEY
,NAME VARCHAR2(60) CHECK (NAME = UPPER CASE) NOT NULL
,TEAM VARCHAR2(12) CHECK (TEAM = UPPER CASE) NOT NULL
,AREA VARCHAR2(10) NOT NULL);


Thank you for advance!!



Re: upper case in create table [message #155925 is a reply to message #155920] Thu, 19 January 2006 13:11 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
CREATE TABLE TT(
NUM NUMBER NOT NULL PRIMARY KEY
,NAME VARCHAR2(60) CHECK (NAME = UPPER(name)) NOT NULL
,TEAM VARCHAR2(12) CHECK (TEAM = UPPER(team)) NOT NULL 
,AREA VARCHAR2(10) NOT NULL);
Re: upper case in create table [message #155932 is a reply to message #155925] Thu, 19 January 2006 13:34 Go to previous messageGo to next message
jchiu183
Messages: 16
Registered: September 2005
Junior Member
Thank you so much!!
Re: upper case in create table [message #155940 is a reply to message #155932] Thu, 19 January 2006 15:39 Go to previous messageGo to next message
Littlefoot
Messages: 21678
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Such a constraint will prevent user to enter lower case values into the table, resulting in an error message (check constraint violated).

There is another possibility which would allow him to enter value either in lower or upper case, but - finally - it would be stored as upper case. To do so, you could create a database trigger, for example:
CREATE TRIGGER trg_tt_name
  BEFORE INSERT OR UPDATE ON tt
  FOR EACH ROW
BEGIN
  :new.name := UPPER(:new.name);
END;

There was discussion somewhere regarding possible overhead when using database triggers (during massive inserts or updates); if I remember well, conclusion was that there's no overhead which should make you worry. I can't prove any of this, but I'd really love to hear what SQL gurus here think about that issue (of course, if any of you reads this Smile).
Re: upper case in create table [message #156033 is a reply to message #155940] Fri, 20 January 2006 06:55 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If the data ***needs*** to be in a certain case(upper,lower,initcap.. whatever) , doing it with table-level constraints is better.
Triggers are painful.
Question is why? Is it really needed?
With the proper usage of function based index, in most cases, the case/format in which the character data is stored does not matter.
Re: upper case in create table [message #684187 is a reply to message #155925] Sun, 18 April 2021 04:03 Go to previous messageGo to next message
Lakshya
Messages: 1
Registered: April 2021
Junior Member
CREATE TABLE CLIENT_MASTER(
client_no VARCHAR(6) NOT NULL PRIMARY KEY CHECK (client_no like 'c%'),
NAME CHAR(20) CHECK (NAME = UPPER(name)),
address_1 VARCHAR(20),
address_2 VARCHAR(20),
city VARCHAR(15),
state VARCHAR(15),
pin_code NUMERIC(6),
remarks INT(10),
bal_due NUMERIC(8,2)
);


its not working yet
Re: upper case in create table [message #684188 is a reply to message #684187] Sun, 18 April 2021 05:00 Go to previous message
Michel Cadot
Messages: 67888
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What do you mean by "its not working yet"?

Previous Topic: Hierarchy again and again and again !
Next Topic: Listagg over Listagg
Goto Forum:
  


Current Time: Thu Jun 24 23:27:26 CDT 2021