We maintain a column in the GIS that tracks the billing number for each facility (asset) we put into the ground. Although there are a large number of billing numbers (probably several thousand at this point), they should all follow the same pattern. To ensure there are any errors when entering them into the GIS, I wrote a check constraint. For those that aren’t familiar with check constraints in Oracle, they’re basically a step below referential integrity. They allow any value to be put into a column as long as the value meets the rules of the check constraint.<\/p>\n
So in my case the billing number is a seven character number following this format "G6-0901". So here’s the constraint that I wrote:<\/p>\n
REGEXP_LIKE (ECM_NUMBER,'[E][6-9]-[0][8-9][0-9][0-9]’) OR REGEXP_LIKE (ECM_NUMBER,'[G][0-9]-[0][8-9][0-9][0-9]’)<\/strong><\/p>\n REGEXP_LIKE – This is a new function release in Oracle 10g. It allows you to use regular expressions in your SQL. In this case, I’m using an Oracle function "REGEXP_LIKE" which allows you to compare a known value against a regular expressiont.<\/p>\n ECM_NUMBER – This is simply the column name where the constraint resides. In other words, this is the name of the column that holds the billing number.<\/p>\n [E] – This ensures that the first character in the number is the letter "E"<\/p>\n [6-9] – This ensures that the second character in the number is a number 6, 7, 8, or 9<\/p>\n "-" – This ensures that the third character is a "dash"<\/p>\n [0] – This ensures the fourth character is a 0<\/p>\n [8-9] – This ensures the fifth character is a 8 or 9<\/p>\n [0-9] – This ensures that the sixth character is a 0 – 9<\/p>\n [0-9] – See above<\/p>\n Now I’ve also placed an OR statement in there providing two different formatting options. The rules are the same, but I’m just allowing for different formatting (e.g. starts with a "G" as opposed to an "E"), etc. I suppose I could have written it into one expression, it was just easier (in my eyes) to put it into two expressions.<\/p>\n Overall, I think it’s also pretty cool that really almost all of this is just regular expressions. So, if you’re familiar with regular expressions and their syntax, these types of constraints (and even MUCH more advanced queries) should be very "simple". \n\t\t\t <\/p>\n","protected":false},"excerpt":{"rendered":" We maintain a column in the GIS that tracks the billing number for each facility (asset) we put into the…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false},"categories":[11],"tags":[],"acf":[],"yoast_head":"\n