Hi Tracy,

This case can be handled by LIKE operator but you can get multiple matches

'4914SMARKETSTSESTE109' LIKE '4914SMARKETSTE109' + '%'

>I realize it won't be optimizable, but I am trying to find a solution to the parsing of address problem. If an address is not validated against our lookup tables (which shouldn't be the case but may very well be as new housing developments are built and hence streets added and the system may not be up to date at all times) then I need some 'just in case you don't validate it' means of seeing if a matching record exists in a 3rd party app's sqlserver (or foxpro) tables. However, it just dawned on me that this is not a solution anyway, because it would only work if the user entered all values:
>
>If a valid address in sql server is: 4914 S MARKET ST SE STE 109
>
>FIELDS:
>
>number = '4914'
>st_prefix = 'S'
>street = 'MARKET'
>st_type = 'ST'
>st_suffix = 'SE'
>apt_room = 'STE 109'
>
>and the user enters:
>
>4914 S MARKET STE 109
>
>'4914SMARKETSTE109' does not equal '4914SMARKETSTSESTE109' anyway.
>
>Thank goodness I do not have a migraine today!
>
>
>
>
>
>
>>Sure you can but it'll not be optimizable. Also if columns are varchar you may not even need to trim them.
>>
>>>SQLSERVER 2000 or 2005.
>>>
>>>Is it possible to do something like:
>>>
>>>
>>>

>>>mvalue = '100'+'SO'+'MAIN'+'ST'
>>>SELECT * FROM mysqltable
>>where alltrim(number)+alltrim(st_prefix)+alltrim(street)+alltrim(st_suffix) = myvalue
>>>

>>>?