OraFAQ Forum: SQL & PL/SQL » upper case in create table

Home » SQL & PL/SQL » SQL & PL/SQL » upper case in create table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

upper case in create table [message #155920]

Thu, 19 January 2006 12:20

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!!

Report message to a moderator

Re: upper case in create table [message #155925 is a reply to message #155920]

Thu, 19 January 2006 13:11

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);

Report message to a moderator

Re: upper case in create table [message #155932 is a reply to message #155925]

Thu, 19 January 2006 13:34

jchiu183
Messages: 16
Registered: September 2005

Junior Member

Thank you so much!!

Report message to a moderator

Re: upper case in create table [message #155940 is a reply to message #155932]

Thu, 19 January 2006 15:39

Littlefoot
Messages: 21760
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

Report message to a moderator

Re: upper case in create table [message #156033 is a reply to message #155940]

Fri, 20 January 2006 06:55

Mahesh Rajendran
Messages: 10706
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.

Report message to a moderator

Re: upper case in create table [message #684187 is a reply to message #155925]

Sun, 18 April 2021 04:03

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

Report message to a moderator

Re: upper case in create table [message #684188 is a reply to message #684187]

Sun, 18 April 2021 05:00

Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 06:34:01 CDT 2023