[Solved] Regular expressions for searching strings?

[onidarbe]

How can I search string variable$ using Regular expressions in Calc/Basic.

[FJCC]

You just need to set the SearchRegularExpression property to true as in this code.

Code: Select all

Doc = ThisComponent
Sheet = Doc.Sheets(0)
Descript = Sheet.createSearchDescriptor
Descript.SearchRegularExpression = True
Descript.SearchString = "5.+9"
Targ = Sheet.FindFirst(Descript)

The Targ variable is the cell found by the search.

[onidarbe]

FJCC, thanks, but again we have a communication problem

I don't need the Regular expressions for searching cells in Calc, but for searching a Variable like MyVar$ in basic code.

[FJCC]

I'm not sure I understand still. The Edit -> Find and Replace function in the Basic IDE has regular expressions in the More Options section. Does that do what you want?

[TheGurkha]

If I understand, you want to search the text of a string that is held in a variable? I think you will have to put that string into a cell and then search the cell.

[onidarbe]

TheGurkha, that's exactly what I mean. It is a solution though I which I wouldn't have to put it in a cell. Specially not because it's a hole website-html

In VBA Excel one can use Regular Expresions with for instance REPLACE, but not in OObasic

[TheGurkha]

Well, I'm no expert on this so maybe someone will surprise me and come up with a way, but as we've had no positive responses so far I think I might be right.

[JohnV]

Using a Basic macro you can deal with strings in variable fields in a Writer document but I don't know want the OP has in his Calc document.

[B Marcelly]

Hi,
You want to search within a String, using a regular expression.
This is possible, but not easy, using the API service com.sun.star.util.TextSearch. Read all linked pages from this documentation page, and test with many examples.

This code works with any OOo document (Writer, Calc...)

Code: Select all

Option Explicit

Sub SearchWithRegExp()
Dim rch As Object, resu As Object, r As Long, x As Long
Dim opts As New com.sun.star.util.SearchOptions
Const maChaine = "Au clair de la lune     mon ami  Pierrot"

rch = createUnoService("com.sun.star.util.TextSearch")
with opts
  .searchString = "a.i"
  .algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
  .searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED
end with
rch.setOptions(opts)
resu = rch.searchForward(maChaine, 0, Len(maChaine))
r = resu.subRegExpressions
if r > 0  then
  for x = 0 to r-1 ' the search may find several results
    ' for each found substring, the position in the string is counted from zero !!
    ' startOffset is the position of the leftmost character in the string
    ' endOffset is the position of the rightmost character +1 !!!
    print "Found :", resu.startOffset(x), resu.endOffset(x)
  next
else
  print "Not found !"
end if
End Sub

______
Bernard

[Villeroy]

Any supported macro language other than Basic supports regexes.
Calling JavaScript from Basic: regular expressions

[onidarbe]

Villeroy, B Marcelly, Thank you both!

This will do for now, until someone finally adds RegEx to the OO-basic
If anyone could report this future, I would very much appriciate it.

[onidarbe]

B Marcelly,
It seams "resu.subRegExpressions" always returns 1 !?
I would suspect to see more "Found:"-lines when just looking for "a". At least that's why there is a "for x = 0 to r-1", isn't it?

[TheGurkha]

Villeroy, B Marcelly, Thank you both!

If anyone could report this future, I would very much appriciate it.

To report bugs or make suggestions, see this tutorial: [Tutorial] Reporting bugs or suggestions

[B Marcelly]

Hi,
I am not expert in reg exp, and the API seems to accept a limited syntax.
You may get several results if you use parentheses in the search string. The results correspond to the sub-sequences in parentheses.
With the above code, test with:

Code: Select all

  .searchString = "a.r( de la)( lune)"

______
Bernard

[onidarbe]

Thanks to B Marcelly and others, I've written some functions to be able to use Regular Expressions in OO-basic.
I am still looking for the complete OO RegEx syntax, because for instance the "(?i)" or "/i" which should turn on case-insensitive doesn't work in OO, like explained at: http://www.regular-expressions.info

Code: Select all

sub sExample
	vString="name:Mike xxx name:Kevin xxx name:Kenny xxx. Noname xxx" 
'	msgbox fRegExpMidStr(vString,"name:"," ") '="Mike"
'	msgbox fRegExpMidStr()   '="Kevin"
'	msgbox fRegExpMidStr()   '="Kenny"
'	msgbox fRegExpMidStr()   '=""
	msgbox fRegExpStr(vString,"[A-Z][a-z]+")  '="Mike"
	msgbox fRegExpStr()   '="Kevin"
	msgbox fRegExpStr()   '="Kenny"
	msgbox fRegExpStr()   '="Noname"
	msgbox fRegExpStr(,"Mike.+Kevin")  '="Mike xxx name:Kevin"
end sub

function fRegExpObj(optional xInStr, optional xSearchRegExp)
	'''Returns the RegularExpression-OBJECT found within a string
	'''If all parameters are missing: find next
	'''If a parameter is missing: last parameter will be used and search again from start
	'''More info on RegEx-syntax: http://www.regular-expressions.info/reference.html
	static vInStr, vNextPos, bCaseInsensitive, oTextSearch, oSearchOptions
	if isEmpty(oTextSearch) then '''initialize objects
		oTextSearch = createUnoService("com.sun.star.util.TextSearch")
		oSearchOptions = CreateUnoStruct("com.sun.star.util.SearchOptions")
		oSearchOptions.algorithmType = com.sun.star.util.SearchAlgorithms.REGEXP
		oSearchOptions.searchFlag = com.sun.star.util.SearchFlags.REG_EXTENDED
	end if
	if not isMissing(xSearchRegExp) then
		if left(xSearchRegExp,4)="(?i)" then '''because this RegEx is missing in OO
			bCaseInsensitive=true
			oSearchOptions.searchString = mid(xSearchRegExp,5)
		elseif left(xSearchRegExp,5)="(?-i)" then '''because this RegEx is missing in OO
			bCaseInsensitive=false
			oSearchOptions.searchString = mid(xSearchRegExp,6)
		else
			oSearchOptions.searchString = xSearchRegExp
		end if
		oTextSearch.setOptions(oSearchOptions)
		vNextPos=0
		bCaseInsensitive=false
	end if
	if not isMissing(xInStr) then
		vInStr=xInStr
		vNextPos=0
		bCaseInsensitive=false
	end if
	if bCaseInsensitive then
		fRegExpObj = oTextSearch.searchForward(lCase(vInStr), vNextPos, Len(vInStr))
	else
		fRegExpObj = oTextSearch.searchForward(vInStr, vNextPos, Len(vInStr))
	end if
	if fRegExpObj.subRegExpressions > 0 then vNextPos=fRegExpObj.endOffset(0)
end function
function fRegExpStr(optional xInStr, optional xSearchRegExp)
	'''Returns the RegularExpression-STRING found within an string
	'''If all parameters are missing: find next
	'''If a parameter is missing: last parameter will be used and search again from start
	static vInStr
	oFound=fRegExpObj(xInStr,xSearchRegExp)
	if oFound.subRegExpressions>0 then
		if not isMissing(xInStr) then vInStr=xInStr
		vStartPos=oFound.startOffset(0)
		vEndPos=oFound.endOffset(0)
		fRegExpStr=mid(vInStr,vStartPos+1,vEndPos-vStartPos)
	end if	
end function
function fRegExpMidStr(optional xBaseStr, optional xStartRegExp, optional xStopRegExp)
	'''Returns the string in between two RegularExpressions found in a string
	'''If all parameters are missing: find next
	'''If some parameters are missing: use last one and start again
	'''If vStartRegExp is "": get string from start
	'''If vStopRegExp is "": get string till end
	static vBaseStr, vStartPos, vStartRegExp, vStopRegExp
	if not isMissing(xBaseStr)     then vBaseStr    =xBaseStr     : vStartPos=0
	if not isMissing(xStartRegExp) then vStartRegExp=xStartRegExp : vStartPos=0
	if not isMissing(xStopRegExp)  then vStopRegExp =xStopRegExp  : vStartPos=0
	if vStartRegExp="" then
		vStartPos=0
	else
		oFound=fRegExpObj(mid(vBaseStr,vStartPos+1),vStartRegExp)
		if oFound.subRegExpressions>0 then
			vStartPos=vStartPos+oFound.endOffset(0)
		else
			vStartPos=len(vBaseStr)
		end if
	end if
	if vStopRegExp="" then
		vStopPos=len(vBaseStr)
		vNextPos=len(vBaseStr)
	else
		oFound=fRegExpObj(mid(vBaseStr,vStartPos+1),vStopRegExp)
		if oFound.subRegExpressions>0 then
			vStopPos=oFound.startOffset(0)
			vNextPos=vStartPos+oFound.endOffset(0)
		else
			vStopPos=0
			vNextPos=len(vBaseStr)
		end if
	end if
	fRegExpMidStr=mid(vBaseStr,vStartPos+1,vStopPos)
	vStartPos=vNextPos
end function