'64 bit regex based on https://community.pitneybowes.com/communities/communityhome/digestviewer/viewthread?MessageKey=a92a0fcd-5348-4eae-aa93-a47d5a1e6bbe&CommunityKey=3c2aca7a-c3ae-4602-a142-9ee956769d55&tab=digestviewer#bma92a0fcd-5348-4eae-aa93-a47d5a1e6bbe'
Include "MapBasic.def"

'64 bit regex'
Declare Method RegExReplace Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String, ByVal sReplace As String) As String

Declare Method RegExIsMatch Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String) As Logical

Declare Method RegExFirstMatch Class "WindowHelper.Controller" Lib "WindowHelper.dll" (ByVal sInput As String, ByVal sPattern As String) As String

Declare Function SearchReplace(ByVal strInput as String, ByVal strReplace as String, ByVal strReplacement as String) as String

'=========================================
'// Replace part of a string with another string - https://gis.stackexchange.com/questions/240034/replace-character-in-string-in-mapbasic
'-----------------------------------------
Function SearchReplace(ByVal strInput as String, ByVal strReplace as String, ByVal strReplacement as String) as String

Dim iPos as Integer
Dim strOut as String
Dim i as Integer

    iPos = InStr(1, strInput, strReplace)
    If iPos < 1 then            '// nothing to replace, return original string
        SearchReplace = strInput
        Exit Function
    End If

    While iPos > 0              '// loop until nothing left to replace
        If iPos > 1 then
            strOut = Left$(strInput, iPos - 1)
        End if
        strOut = strOut & strReplacement
        If iPos + Len(strReplace) - 1 < Len(strInput) then
            strOut = strOut & Mid$(strInput, iPos + Len(strReplace), Len(strInput) - (iPos + Len(strReplace)) + 1)
        End If

        strInput = strOut
        iPos = InStr(iPos + 1, strInput, strReplace)
    Wend

    SearchReplace = strOut

End Function


Declare Sub Main

Sub Main
	Dim inFile, inField, regexString as String
	Dim inColumn as Alias
	
	inFile = "Z:\My Drive\Mangoesmapping\Spatial Projects\2018\DSC\007_DSC_Asset_Identification_and_Geocoding\Working\Phase3_DataEnrichment\Working\DataUpdates\Scratch\Water_Hydrants.TAB"
	inField = ".As_con_3_hyperlink"
	'Perform Regex and text Replace'
	Open Table inFile as tempTable
	'Alter Table tempTable (add ScratchField Char(254) ) Interactive
	Browse * From tempTable
	'Pattern for starting | without anything before it
	regexString ="(.*)[\s]+\|\s(.*)"
	'Pattern to get rid of blank xxx fields
	'regexString ="[a-zA-Z0-9]?DSC COMMENTS FIELD = \|(.*)"
	'regexString ="PIPE_TYPE_FROM_DOC: \s\|\s(.*)"
	'regexString ="DWG_ORIGIN: \s\|\s(.*)"
	'regexString ="Condition: \s\|\s(.*)"

	
		
	' Perform on all fields -ref: https://gis.stackexchange.com/questions/75494/how-to-loop-through-all-columns-in-a-table-layer-getting-all-data-for-a-row-feat	
	Dim sCol As String
	Dim aCol As Alias
	Dim nCol As Integer
	For nCol = 1 To TableInfo(tempTable, TAB_INFO_NCOLS)
		sCol = ColumnInfo(tempTable, "COL" & nCol, COL_INFO_NAME)
		aCol = tempTable & "." & sCol
	'Update every column as it's found --- GIVES ERROR ref: https://gis.stackexchange.com/questions/316048/get-code-to-run-on-every-column-in-a-table-error-could-not-find-a-public-stati	   	
		'Update tempTable Set ScratchField = RegExReplace(aCol,regexString,"")     	
		Next
		
	'Update a known column
	inColumn = tempTable & inField
	Update tempTable Set ScratchField = RegExReplace(inColumn,regexString,inColumn)
'	select * from tempTable where ScratchField like "%|" into tempTable2
'	Browse * from tempTable2
'	inColumn=tempTable2 & inField
'	Update tempTable2 Set ScratchField = SearchReplace(inColumn," |","")
	'Commit Table temptTable
	
End Sub