>>>Hi
>>>
>>>I've wriiten a Foxpro proceedure in VFP9 that allows the user to build up a command using different selected search criteria, this then creates an SQL type command that extracts data from a massic Postcode table.
>>>
>>>E.g.
>>>
>>>mfullcond="SUBS(DMOS,4,1)$AB and left(f17,1)='0' "
>>>SELECT * from q.dbf WHERE &mfullcond INTO TABLE results
>>>
>>>The customer is now interested in transfering the data into an SQL 2008 server, are the same/similar commands available with real SQL 2008.
>>>
>>>Is it possible to do contained within in SQL, and can you do left in SQL 2008?
>>>
>>>Any help extremley appreciated
>>>
>>>Rob
>>
>>
>>No, there is no macro substitution in SQL Server (no matter which version).
>>You have to use so called Dynamic SQL (buk,d the whole query in single string and then use EXEC () )
>>Also SUBS() in VFP = SUBSTRING() in SQL Server (LEFT() is the same)
>>Also there is no [$] you have to use LIKE or CHARINDEX()
>>

>>SELECT * from Table WHERE Ab LIKE '%' + SUBSTRING(DMOS,4,1) + '%'
>>

>>or
>>

>>SELECT * from Table WHERE CHARINDEX(SUBSTRING(DMOS,4,1), Ab) > 0
>>

>
>Thanks Boris
>
>So this is doable then, just need to use the commands you given me.


Sure, it is one way or another.
I prefer to stay away from Dynamic SQL, but there are times when it is not possible :-)
>
>Many thanks
>
>Rob