Foxpro Regular Expression

Below code converts Sql code generated for Insert from Management studio into Foxpro code.
Right click on a table in SSMS -> Script table As -> Insert To -> New Query Editor Window

Below was generated from Publishers table from Pubs database

INSERT INTO [pubs].[dbo].[publishers]
([pub_id]
,[pub_name]
,[city]
,[state]
,[country])
VALUES
(<pub_id, char(4),>
,<pub_name, varchar(40),>
,<city, varchar(20),>
,<state, char(2),>
,<country, varchar(30),>)


Select all & Copy

In foxpro command window
lcStr = _cliptext
This will put the generated code in clipboard.



loR = CREATEOBJECT('vbscript.regexp')
lor.Global = .t.
lor.Multiline = .t.
lor.IgnoreCase = .t.
lor.Pattern = "(,\s\w+\(?\d*\)?,\>)"
lcstr = lor.Replace(lcSTr, '')
lcstr = STRTRAN(lcStr, '<', '?')

This is what will be the value of lcStr after executing above code

INSERT INTO [pubs].[dbo].[publishers]
([pub_id] ,[pub_name] ,[city] ,[state] ,[country])
VALUES
(?pub_id ,?pub_name ,?city ,?state ,?country)