wood burning stoves 2.0*
The moose likes JDBC and the fly likes Column Constraint Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Column Constraint" Watch "Column Constraint" New topic

Column Constraint

John Culling

Joined: Aug 19, 2012
Posts: 1
I am trying to create a column constraint in Apache Derby to check that the input data is in the form of three uppercase alpha characters plus a three digit number e.g. ABC100 or XYZ999.
I have the following, which works in MS Server

CONSTRAINT valid_consultant_id
CHECK ((ASCII(SUBSTRING(consultant_id,1,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,2,1)))BETWEEN 65 AND 90 AND
(ASCII(SUBSTRING(consultant_id,3,1)))BETWEEN 65 AND 90 AND
(CAST(SUBSTRING(consultant_id,4,3)as smallint)BETWEEN 100 AND 999))

however, Apache Derby does not seem to recognise the ASCII keyword.

I know I could do
CHECK (SUBSTR(consultant_id,1,1)) IN ('A','B','C'......................'Z') AND..............

but that seems very clumsy.

Can someone point me in the direction of a more elegant solution please
chris webster

Joined: Mar 01, 2009
Posts: 1621

Can't help with Apache Derby, but as you're having to look at platform-specific options anyway, could you use a regular expression instead?

No more Blub for me, thank you, Vicar.
Rob Spoor

Joined: Oct 27, 2005
Posts: 19655

Perhaps the following will work:
Unfortunately, that probably ignores the case so you'd have to figure out how to fix that issue.

How To Ask Questions How To Answer Questions
Don't get me started about those stupid light bulbs.
subject: Column Constraint
Similar Threads
Words with capital letters
best way to check whether a character is uppercase?
Apache Derby Sql
converting ascii number to capital letter
Validations for name in text only