I only need it because when the 3rd party provided us with a copy of their live data, there were over 1000 records stored with spaces at the beginning of the street name and on the number field, in the city field, etc. It appears that what you type is what you get in that system, but we are pulling data from it for view purposes only so I have to work with what they have.


>Hi Tracy,
>
>Do you realy need LTRIM() on the colums? I don't think data should be stored with leading spaces.
>
>>OK, a valid address is:
>>
>>5614 HIGH POINT RD
>>
>>This works:
>>
>>declare @cvalue varchar(15)
>>set @cvalue = '5614'
>>select * from occ_main where ltrim(rtrim(number))+ltrim(rtrim(st_prefix))+ltrim(rtrim(street))+ltrim
>>(rtrim(st_type))+ltrim(rtrim(st_suffix))+Ltrim(rtrim(apt_room)) LIKE '%' + ltrim(rtrim(@cvalue)) + '%'
>>
>>and this works:
>>
>>declare @cvalue varchar(15)
>>set @cvalue = 'HIGH'
>>select * from occ_main where ltrim(rtrim(number))+ltrim(rtrim(st_prefix))+ltrim(rtrim(street))+ltrim(rtrim(st_type))+ltrim(rtrim(st_suffix))+Ltrim(rtrim(apt_room)) LIKE '%' + ltrim(rtrim(@cvalue)) + '%'
>>
>>and this works:
>>
>>declare @cvalue varchar(15)
>>set @cvalue = '5614HIGH'
>>select * from occ_main where ltrim(rtrim(number))+ltrim(rtrim(st_prefix))+ltrim(rtrim(street))+ltrim(rtrim(st_type))+ltrim(rtrim(st_suffix))+Ltrim(rtrim(apt_room)) LIKE '%' + ltrim(rtrim(@cvalue)) + '%'
>>
>>but this does not:
>>
>>declare @cvalue varchar(15)
>>set @cvalue = '5614 HIGH'
>>select * from occ_main where ltrim(rtrim(number))+ltrim(rtrim(st_prefix))+ltrim(rtrim(street))+ltrim(rtrim(st_type))+ltrim(rtrim(st_suffix))+Ltrim(rtrim(apt_room)) LIKE '%' + ltrim(rtrim(@cvalue)) + '%'
>>
>>So I guess I have to remove ALL spaces in the value entered to pull matching records. Should've figured that...
>>
>>(The code was cut from sql sever management studio - not vfp)