Wiki Home

VFPSQL - TSQL - Mapping


Namespace: VFP

VFP/SQL functions concordance


This is a table to help people that are converting VFP SQL Statements to SQL Server T-SQL statements.


Set of VFP string functions implemented in T-SQL by Brad Schulz

Handy String Functions


TYPEVFPT-SQL
AutoIncrement field last valueGETAUTOINCVALUESCOPE_IDENTITY() / OUTPUT clause in SQL Server 2005 and up.

See also:
Find the IDENTITY value of the last inserted row (discussion on @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(), and which one to use).

See also http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
Inline ConditionIIF(condition, true value, false value)CASE WHEN condition THEN true value ELSE false value END
BETWEEN BETWEEN(field, low value, high value)field BETWEEN lowvalue AND highvalue. Better yet, just use the SQL BETWEEN instead of the VFP between() in all cases when writing SQL (VFP or NOT)
True/False.T. / .F.No logical data type
1=1/1=2
Check something is NULLISNULL(checked_value)IS [NOT] NULL this syntax worked in VFP also
Get non NULL value from two expressionsNVL(checked_value,value_if_expression_is_null)

ISNULL(checked_value,value_if_expression_is_null)

Also COALESCE ( expression [ ,...n ] ) - returns the first nonnull expression among its arguments.


String comparison = for partial, == for exact. LIKE is supported since at least VFP v7 -- Mike Yearwood LIKE
Empty Dates{}Not supported. Use NULL instead
Checking for an empty stringEMPTY(fieldname). EMPTY() has never been Rushmore optimizable. Try FieldName = SPACE(LEN(FieldName)) or UPPER(FieldName) = SPACE(LEN(FieldName)) especially if you have an index on UPPER(fieldname). -- Mike Yearwood LEN(FieldName) = 0
Current Date and TimeDateTime()GetDate() or DATEADD(ms,-DATEPART(ms,GETDATE()),GETDATE()) because GetDate() return datetime with ms
Current DateDate()Date data type doesn't exist
In SQL Server 2008 there are new DATE and TIME types
Add/Subtract from a dateUse arithmetic operators Date() + 1 or Date() - 1 DATEADD(day,1,Getdate()) or to substract use a negative integer
You can directly add/subtract dates from getdate():
select getdate() - 31, getdate() + 30
Regarding the last comment - you can not do this with Dates in SQL Server 2008
see Compatibility Clash in 2008
Compare two dateslogic symbols<,>,=DATEDIFF ( datepart , startdate , enddate )
Date constant or literalcurly braces and an uproot example: {^2006/01/31} Several format with single quotes example: 'April 15, 1998'
'15 April, 1998'
'980415'
'04/15/98'. However, only ISO constants are safe, e.g. only this type of constant '20101201' can be used in queries for the dates.
Get the integer portion of a numberINT()CAST(somenumber AS int)
Convert date to sortable stringDTOS(date-value) CONVERT(char(8), date-value, 112)
Replace text in a stringSTRTRAN()REPLACE()
Trim stringLTRIM(), TRIM() or RTRIM(),
ALLTRIM()
LTRIM(), RTRIM(),
LTRIM(RTRIM())
Number of occurrences of a character in a stringOCCURS()
How many occurrences in a string
Pad a string with spacePADR(somestring,numchars)
PADL(somestring,numchars)
CAST(somestring AS char(numchars))
REPLICATE(SPACE(1), numchars - DATALENGTH(somestring )) + somestring , PADR(),PADL(),PADC()
Convert an integer to fixed length zero paddedPADL(intval,numchars, '0')
REPLICATE('0', numchars - DATALENGTH( CAST(intval AS varchar(numchars)) )) + CAST(intval AS varchar(numchars))
Find text in a stringAT()
$
CHARINDEX(), PATINDEX()
LIKE
Return a string within in a stringSUBSTR()SUBSTRING()
A string lengthLEN()DATALENGTH()
LEN() - doesn't count trailing spaces
Checking a list of values[NOT] INLIST(valuelookingfor, testval1, testval2, testval3)
IN / NOT IN works in VFP also
valuelookingfor [NOT] IN (testval1, testval2, testval3)
If value is in another table, then INNER JOIN / LEFT JOIN with IS NULL may be a better alternative
Deleting all recordsZAP [IN nWorkArea | cTableAlias] TRUNCATE TABLE tablename
Determine last query rowcount_TALLY@@ROWCOUNT
Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.CHRTRAN() REPLACE ( string_expression , string_pattern , string_replacement )
Removes all characters from a string except those specified.
CHRTRAN(cString, CHRTRAN(cString, cFilter, ""), "")Igor's Nikiforov UDF StrFilter
Counts the words in a string.
GETWORDCOUNT()Igor's Nikiforov UDF GetWordCount
Returns a specified word from a string.
GETWORDNUM()Igor's Nikiforov UDF GetWordNum

Other references:
- http://www.ml-consult.co.uk/foxst-38.htm
Contributors Sergey Berezniker Igor Nikiforov Evan Delay Mike Yearwood Rick Bean Ben Creighton

Category Client / Server Category 3 Star Topics
Category SQL



( Topic last updated: 2013.08.21 04:16:29 PM )