SQL Index question

[RichAlgeni]

I need to create an index and lookup for street address ranges. This has to contain user specific data, which is proprietary to the client, and not available online. The problem occurs with the mixing of right and left justified data. Right for the street number, and left for the street name. For instance, '100 Main St' should precede '101 Main St', and not '1000 Main St'. Here is what you get when you sort the data:

Code: Select all

10 Main St
100 Main St
1000 Main St
101 Main St
102 Main St
103 Main St
104 Main St
105 Main St
106 Main St
107 Main St
108 Main St
109 Main St
11 Main St

One idea I had was to use a 6 digit numeric string for the street number, appended with a space to the Street name. For instance '000100 Main St' will certainly precede '000101 Main St'. Thoughts? Sorting this way yields much better results:

Code: Select all

000100 Main St
000101 Main St
000102 Main St
000103 Main St
000104 Main St
000105 Main St
000106 Main St
000107 Main St
000108 Main St
000109 Main St
000110 Main St
000111 Main St
000112 Main St

One issue with this is that it is going to take up more disk, but that shouldn't be too much of a problem. I really don't need every address in the range, only those where other information changes, such as cross streets, city grids, etc.

I was also wondering if you would recommend that I store street names as Soundex values?

Thanks for any assistance you can offer.

Rich

[citystate]

you could separate the street number from the street name - keeping the number as an integer would probably solve it

[RichAlgeni]

Didn't thank you for the response citystate, sorry about that..., and thanks!

I had to put this off for a bit, but I've since come up with a process to standardize words, like 'St', to 'STREET', changing alphas to upper case, expanding the street number to 6 digits, converting words using metaphone, adding 'O' or 'E' to the beginning, depending on the street number, sorting the words, then concatenating everything together.

105 s main st

to this:

105 SOUTH MAIN STREET

to this:

O000105MNS0
O000105MNSTRT
O000105S0STRT
O000105MNS0STRT

Hopefully, this will handle searches correctly!