SQLite: Difference between revisions

From Starfish ETL
Jump to navigation Jump to search
No edit summary
No edit summary
Line 1: Line 1:
=DateTime Fields=
Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html.
Starfish's FormatDate function can help with this.  See this vbscript for an example:
<source lang="mysql">
Sub VBScriptProcedure
'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss")
'LogMessage FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")
If PreviewMode <> True Then
dim b
b = SQLiteUpdate("contacts_to_update_from_leads","processed='1', date_modified='"&FormatDate(Now, "yyyy-MM-dd HH:mm:ss")&"'","contactid='@@ORG:contactid@@' and leadid='@@ORG:leadid@@' and (processed<>'1' or processed is null)")
dim d
d = SQLiteDelete("contacts_to_update_from_leads","processed='1' and date_modified<'"&FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")&"'")
End If
End Sub
</source>
=Levenshtein=
=Levenshtein=
The SQLite connector has the [https://en.wikipedia.org/wiki/Levenshtein_distance Levenshtein] function!  A couple of examples:
The SQLite connector has the [https://en.wikipedia.org/wiki/Levenshtein_distance Levenshtein] function!  A couple of examples:

Revision as of 20:34, 1 April 2019

DateTime Fields

Note that when working with "DateTime" fields in SQLite, the value saved in the field and used for comparison operations must match one of the 10 allowed formats: https://www.sqlite.org/lang_datefunc.html. Starfish's FormatDate function can help with this. See this vbscript for an example:

Sub VBScriptProcedure
'LogMessage FormatDate(Now, "yyyy-MM-dd HH:mm:ss")
'LogMessage FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")
	If PreviewMode <> True Then
		dim b
		b = SQLiteUpdate("contacts_to_update_from_leads","processed='1', date_modified='"&FormatDate(Now, "yyyy-MM-dd HH:mm:ss")&"'","contactid='@@ORG:contactid@@' and leadid='@@ORG:leadid@@' and (processed<>'1' or processed is null)")	
		dim d
		d = SQLiteDelete("contacts_to_update_from_leads","processed='1' and date_modified<'"&FormatDate(DateAdd("yyyy",-1,Now), "yyyy-MM-dd HH:mm:ss")&"'")
	End If
End Sub


Levenshtein

The SQLite connector has the Levenshtein function! A couple of examples:

SELECT LEVENSHTEIN( 'Microsoft', 'Technology Advisors, Inc.' )

Result: 21

SELECT LEVENSHTEIN( 'Technology Advisors Inc', 'Technology Advisors, Inc.' )

Result: 2

Comparing 2 strings is obviously less than useful, so you would want to do something like:

SELECT * FROM TABLE WHERE LEVENSHTEIN('Technology Advisors, Inc.', TABLE.NAME) < 5

Issues

If your source SQLite table has a DateTime field, you may get the error: “String not recognized as a valid datetime”. If you get this error, add "datetimeformat=CurrentCulture", without double quotes, to the "Additional Connection String Parameters:" field on the Origin tab.