Some characters not permitted in columns

Question

Some characters not permitted in columns

Robert Jubb

SSC Enthusiast

Points: 114
More actions
September 15, 2006 at 5:22 am

#61822

Hi,
I need to prevent some characters from being entered into specific colunms in a table. It would also be nice to be able to alert me when these characters are entered. Anyone any ideas ?

Kindest Regards,
BJ

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Robert

If you want to be alerted when someone is trying to enter invalid data, then you'll need to create a trigger on the table. Otherwise, you can create a check constraint on the column(s). Look up the ALTER TABLE and LIKE keywords in Books Online.

John

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 2

Robert

If you want to be alerted when someone is trying to enter invalid data, then you'll need to create a trigger on the table. Otherwise, you can create a check constraint on the column(s). Look up the ALTER TABLE and LIKE keywords in Books Online.

John

Robert Jubb SSC Enthusiast Points: 114 More actions · Answer 3

John,

Thank you for your reply. Is there away that the offending characters can be removed. The data is imported from another db and certain characters should be removed.

Kindest Regards,

BJ

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

The REPLACE statement is what you are looking for. You can replace certain characters with an empty string to get rid of them. Also, you can nest the statements to remove more than one character at a time. For example, to lose all Xs and Ys:

update MyTableset MyCol = replace(replace(MyCol, 'X', ''), 'Y', '') 
John

Robert Jubb SSC Enthusiast Points: 114 More actions · Answer 5

Hi John,

Thanks for your reply. That works a treat. I have been asked additionally to be able to provide the details of the row that was affected by the insert. This info would need to be included in the autogenerated email sent. Any further help would be much appreciated.

Thanks

Kindest Regards,

BJ

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 6

If you have already done the update, you will not be able to do what you have been asked. You'd have to restore a copy of the database from a backup taken before the update was done. If you haven't done it, then do something like this just before you do the update

SELECT * FROM MyTable

WHERE MyCol LIKE '%X%'

OR MyCol LIKE '%Y%'

Do this after you've created your constraint or trigger, otherwise invalid data may be entered between you running the select statement and the update.

John

Robert Jubb SSC Enthusiast Points: 114 More actions · Answer 7

Hi John,

Thank you for replying,

The attempted change will be the result of an import from and oracle db which is an automated process.

Is it possible to prevent the character from being entered, and send an auto generated email that specifies that the attempt to enter the data was made, which row in the target db would have been affected, if the attempted change was due to an update or insert statement and date and time info. Also the user details

Thanks

Kindest Regards,

BJ

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 8

You should be able to do that with a trigger. Unfortunately I'm not the person to ask about that. I recommend that you spend half a day reading about triggers and SQL Agent Mail (if you don't already have that set up) then try to come up with a solution. If you run into difficulties then start a fresh thread on this forum, giving as much information as possible - table definitions, sample data, what you've come up with so far, and so on.

John