CODEWITHSUNDEEP
sqlsubstringcheck-constraints
jvlarsen
jvlarsen

Reputation: 149

SQL Check constraint on substrings

Forgive me, if this problem is solved in another ticket on SO... I've been searching, but can't seem to find quite the right solution...

I am creating a table. Column 'Creditor' is numeric all the way, EXCEPT that the very last char may be a dash.

This means that examples like '1234-', '92846293' and so on, are valid, and that '12354-53', '12345K' are invalid. The string length is not fixed (except it's a varchar(50)).

I don't know how to create the check constraint.

Please help!

Upvotes: 1

Views: 4504

Answers (3)

jvlarsen
jvlarsen

Reputation: 149

Thank you for your replies.

The proposal on '%[^0-9]%' was a nice eye-opener for me, as I didn't know the ^ operator before.

I did two versions of the required constraint. One using "only" AND, OR, substrings and isnumeric. No fancy indices or exclusions. Was waaaay too long. The other version consisted of AND, OR, substrings and insmuric, but with the inclusion of the proposed ^ operations. Looks a lot nicer.

Then, in the end, I went with a third solution :-) Added a bool column on the DB, RequiresCreditorValidation, and implemented a Regex in my C# code.

For others hoping to benefit from the resulting checks, here they are. Starting with the "ugly" one:

CHECK ((val NOT IN ('+','-') AND (ISNUMERIC(val) = 1) OR 
(ISNUMERIC(SUBSTRING(val, 1, DATALENGTH(val) -1))) = 1) AND 
((SUBSTRING(val, (DATALENGTH(val)),1) LIKE '[0-9]') OR 
(SUBSTRING(val, DATALENGTH(val),1) = '-')) AND 
(SUBSTRING(val, 1, 1) NOT IN ('+','-')) )

The second one:

CHECK ( (SUBSTRING(val, 1, DATALENGTH(val) - 1) NOT LIKE '%[^0-9]%') AND 
(SUBSTRING(val, DATALENGTH(val),1) LIKE '%[0-9-]') AND (DATALENGTH(val) > 0) 
AND SUBSTRING(val, 1,1) NOT IN ('+','-') )

And then the Regex:

var allButLast = kreditorId.Substring(0, kreditorId.Length - 1);
        if (Regex.Match(allButLast, "[^0-9]").Success)
            return false;
        if (!kreditorId.EndsWith("-"))
            if (Regex.Match(kreditorId, "[^0-9]").Success)
                return false;
        return true;

Thank you all for good, qualified and quick replies.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57023

For SQL Server:

Test 1: all characters apart from the last are numeric (alternatively, there does not exist a character that is non-numeric):

CHECK ( SUBSTRING(Creditor, 1, LEN(Creditor) - 1) NOT LIKE '%[^0-9]%' )

Test 2: the last character is either numeric or a dash:

CHECK ( SUBSTRING(Creditor, LEN(Creditor), 1) LIKE '%[0-9-%]' )

The above assumes Creditor cannot be the empty string i.e. 

CHECK ( LEN(Creditor) > 0 )

Just for fun:

CHECK ( REVERSE(CAST(REVERSE(Creditor) + REPLICATE(0, 50) AS CHAR(50))) 
           LIKE REPLICATE('[0-9]', 49) + '[0-9-]' )

Upvotes: 0

user330315
user330315

Reputation:

You did not state your DBMS. For PostgreSQL this would be:

alter table foo 
  add constraint check_creditor check (creditor ~ '^([0-9]+)\-?$');

For Oracle this would be:

alter table foo 
   add constraint check_creditor check (regexp_like(creditor, '^([0-9]+)\-?$'))

If your DBMS supports regular expressions, you will need to use the syntax for your DBMS to check this. The regular expression itself '^([0-9]+)\-$' will most probably be the same though.

Upvotes: 2

Related Questions