Special Characters in a Database

[JoeBorgione]

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‌

[ChrisDonohue__GISP]

I'm not sure if it still holds up in this day and age, but on the database side memory usage is one reason.  For example, if all the values in a field represent dollars, there is no reason to have the $ sign stored with the data, as all those dollar signs take up space.  There is just lots of redundancy there that one could normalize down to a field without them and save space/make the database work more effectively.

Depending upon your data, a similar argument can be made for the hastag or other symbols; if all the data for a field would have that character anyways, drop that character.  Of course, one would have to have a process to add that character back in if the data needed to be exported to someone and they are expecting the character to included with each value.

The GIS reasoning for not using special characters is that they can cause issues with many GIS processes.  Some processes crash if special characters are included.  Part of this due to the way operating systems work in that they often reserve those characters for operating system commands, so the operating system gets confused when it sees those characters in data.

Chris Donohue, GISP

[RebeccaStrauch__GISP]

Like you, I personally would try to avoid them, but not sure if in the modern world it is mandatory.  But I think it does depend on the field type...string, probably not as much of an issue.  String that is used as a hyperlink? Sone special characters may cause unexpected escape characters, etc.

re the # and historical GIS processes, '#' was used many times to mean "no argument'.  I still use that in my scripts, although not sure if it is a requirement anymore.  

So, it may be more of a concern on how/where it is used in a field.  Those are my thoughts anyway...but although I used/administer SDE in SQL, I can not say I am a database guru.

[JoeBorgione]

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....

[KevinDunlop]

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]

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