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)