Special Characters in a Database

Question

I've always been of the opinion that special characters ( # - $ & etc) have no business in a database.  Probably way back in my formative years someone passed this along, but my formative years were a long time ago.

I have to present an argument to drop at least one special character ( the pound, number or hash tag:  # ).  Other than "I don't like 'em in a database" what logical arguments can be used to sway the users away from them?

Dan Patterson‌

deano2020

rastrauch‌

cdonohue_roseville‌

JoeBorgione · Answer

Thanks Kevin- that's where/how I would prefer to have them as well.

KevinDunlop · Answer

What we do is that we seperate each of the address parts into different fields.

ST_NO (ex 1234)

ST_Name (exS Main)

ST_Type (ex St)

UNIT_NO (ex 1)

This allows the type of queries you are interested in very easily.  If you need the full address, then it is just a matter of concatenating them.

As for the special characters, you could strip them on input (insert trigger) or strip them in the where clause.  Depending on the database you are using, the functions you will need should be pretty straight forward to find a # and remove everything behind it.

JoeBorgione · Answer

The issue at hand is in addresses (what else do I work with,right?).

Currently the database may have 1234 S Main St #1 and 1234 S Main St #2 etc.  Those are in a string field I'll call FullAddress.  Something else I've stumbled upon is a single point may contain a range of house numbers so the HouseNumber (another text field) may look like this 1200-1235.

Finally the application I'm working on needs to be able to:

a. Select an address easy enough: Where Full_Address = '1234 S Main St'

b.  Select additional addresses on S Main St where HouseNumber <= 1234 + 100 and  HouseNumber => 1234 -100

It's a little sticky working mathematical functions on a text field....