Validation on text - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Validation on text

Author

Topic

barrington
Starting Member

3 Posts

Posted - 2012-09-20 : 05:32:57

Hi
I have an insert proc that needs validation on an address. There is a column Line1 varchar(30). I need to add validaton that the users may only type in P.O. Box when entering a Post Box address, they cannot enter something like PO BOX. Obviously the number of the P.O. Box needs to follow, ie, P.O. Box 21.
Can anyone help please?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 07:39:14

Are they allowed to enter an address that is not a P.O. Box address? For example, a street address? If that is allowed, I can't think of any simple way to enforce this condition. If only P.O. Box addresses are allowed, you can check whether the string is like 'P. O. Box%'


CASE WHEN Line1 LIKE 'P.O. Box%' THEN 'Valid' ELSE 'Invalid' END

barrington
Starting Member

3 Posts

Posted - 2012-09-20 : 07:51:29

Thanks a lot, yes they can add a normal residential address as well.

barrington
Starting Member

3 Posts

Posted - 2012-09-20 : 08:10:19

I figured it out except it takes a while to go through the lines and bring back the error or insert a record if it is correct.

I declare line1 as another variable @ivc30_Line1POBox and then say this:

SELECT @ivc30_Line1POBox = @ivc30_Line1

update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P O BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'PO BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O.BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O. BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O.P.O. Box','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'BO BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O P.O. Box','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P0 BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P.O. P.O. Box','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'P. O. BOX','P.O. Box')
update MemAddr set @ivc30_Line1POBox = replace(@ivc30_Line1POBox,'C/O P.O. Box','P.O. Box')

IF @ivc30_Line1POBox <> @ivc30_Line1
BEGIN
RAISERROR ( 'Invalid input of P.O. Box - should be P.O. Box' , 16, 1 )
ROLLBACK TRANSACTION
RETURN -1
END

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-20 : 09:35:04

That should work for the most part - unless you happened to have street names or other parts of the address that match one of the strings you are replacing - for example, Box Street, Brooklyn, NY 11222.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-20 : 09:51:33

For what it's worth, if this is important enough you may want to consider a separate column to hold the PO Box number only, separate from the rest of the address. That eliminates the worry of how "PO Box" is formatted. We had this in an address table and it worked quite well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources