May 4, 2021 at 8:47 pm
Hi,
I am trying to load data from files to tables. One of the columns is a zip code column which in a few rows has zip code extensions (zip+4 in format 12345-7890). I have made the column a number column. I can make it a varchar column but I only want to allow a '-' and not any other character. I think I can build a constraint, but not sure of the syntax. Somewhere online someone mentioned about having a number only in a character column with constraint NUMBER_COL like '%[^0-9]%'. I think a modified ver of this will work, but cannot seem to find it. Can anyone help please.
Ram
May 4, 2021 at 9:39 pm
I think an article that would be helpful is this one:
Basically, you are wanting your LIKE syntax to determine if it is numeric OR numeric with a - in it. If you are wanting something like:
[ZIP] LIKE '[0-9][0-9][0-9][0-9][0-9]' OR [ZIP] LIKE '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
Not the most elegant looking solution, but it is saying that the ZIP MUST be 5 digits that are numeric OR it is 5 digits followed by a - followed by 4 digits. No other formats are allowed with my above example. I think this should meet your requirements. Mind you, if you have a lot of data, this may not be the most efficient way to approach it.
My opinion, I would MUCH rather pull the data into the table as is as a VARCHAR with no constraint (to allow the import to succeed every time) and have a calculated column handle if the ZIP to get it into the proper format. This way when you are consuming the data, you would use the calculated column to read the ZIP and you would just have the calculated column put in "NULL" or "INVALID" when bad data is found for a zip code. This has the advantage of allowing your import to be automated and a report can be sent out after the import to let you know how many invalid ZIP codes were found. The risk of having the constraint on the column is that you MAY end up having data missed if someone makes a typo (such as 12345_7890 or 12345-789) or the format changes in the future and you are not notified and thus you don't update the automation.
Your use case MAY require the import to fail with bad data though. I am not sure on what your process is.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply