This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
vba:functions:rxlib [03.10.2016 15:16:30] yaslaw created |
vba:functions:rxlib [30.01.2017 12:58:55] (current) yaslaw [[VBA] rxMatch() / rxLokup() / rxReplace()] |
||
---|---|---|---|
Line 2: | Line 2: | ||
version=1.0.0 | version=1.0.0 | ||
vdate=03.10.2016 | vdate=03.10.2016 | ||
- | fname=udf_rxmatch.bas | + | fname=lib_rxlib.bas |
ns=%NAMESPACE% | ns=%NAMESPACE% | ||
fpath=/vba/functions | fpath=/vba/functions | ||
</const> | </const> | ||
- | ====== [VBA] rxMatch() ====== | + | ====== [VBA] rxMatch() / rxLokup() / rxReplace() ====== |
+ | Prüft ob ein Value auf ein Pattern passt. Die RegExp-Objekte werden gecached. Interessant für SQL | ||
==Version %%version%% - %%vdate%%== | ==Version %%version%% - %%vdate%%== | ||
Line 12: | Line 13: | ||
{{%%fname%%|Download %%fname%% (V-%%version%%)}} | {{%%fname%%|Download %%fname%% (V-%%version%%)}} | ||
+ | Manchmalwäre es ganz praktisch in SQL mit Regulären Ausdrücken arbeiten zu können. Dazu habe ich eine Funktionssammlung erstellt, die es mit erlaubt genau dies zu tun. Die RegExp-Objekte werden dabei als statische Objekte gespeichert, damit nicht für jede Zeile ein neues Objekt angelegt werden muss. | ||
+ | ===== rxMatch() ===== | ||
+ | ==== Definition ==== | ||
+ | <code>Boolean = rxMatch(value [,pattern])</code> | ||
+ | <code vb>Public Function rxMatch( _ | ||
+ | ByVal iValue As Variant, _ | ||
+ | Optional ByVal iPattern As String = Empty _ | ||
+ | ) As Boolean</code> | ||
+ | ***iValue** Wert, der geprüft werden soll | ||
+ | ***iPattern** Reg-Exp-Pattern gemäss cRx(): [[vba:cast:cregexp#beispiele_mit_patterns_inkl_den_parametern|cRegExp() Beispiele mit Patterns inkl. den Parametern ]] | ||
+ | ==== Beispiele rxMatch() ==== | ||
+ | <code sql>SELECT t.* | ||
+ | FROM myTable t | ||
+ | WHERE rxMatch(t.username, '/^ruedi$/i'); | ||
+ | |||
+ | ID | USERNAME | ||
+ | ---|--------- | ||
+ | 7 | Ruedi | ||
+ | |||
+ | |||
+ | SELECT t.* | ||
+ | FROM myTable t | ||
+ | WHERE rxMatch(t.username, '/ruedi/i'); | ||
+ | |||
+ | ID | USERNAME | ||
+ | ---|----------- | ||
+ | 4 | Hansruedi | ||
+ | 5 | Hans-Ruedi | ||
+ | 7 | Ruedi | ||
+ | |||
+ | |||
+ | SELECT t.*, | ||
+ | rxMatch(t.username, '/ruedi/i') AS like_ruedi, | ||
+ | rxMatch(t.username, '/ueli/i') AS like_ueli, | ||
+ | rxMatch(t.username, '/hans/i') AS like_hans, | ||
+ | rxMatch(t.username, '/hans-?(ueli|peter)/i') AS test4 | ||
+ | FROM myTable t; | ||
+ | |||
+ | ID | USERNAME | like_ruedi | like_ueli | like_hans | test4 | ||
+ | ---|------------|------------|-----------|-----------|------ | ||
+ | 1 | Hans | 0 | 0 | -1 | 0 | ||
+ | 2 | Hans-Ueli | 0 | -1 | -1 | -1 | ||
+ | 3 | Hansueli | 0 | -1 | -1 | -1 | ||
+ | 4 | Hansruedi | -1 | 0 | -1 | 0 | ||
+ | 5 | Hans-Ruedi | -1 | 0 | -1 | 0 | ||
+ | 6 | Ueli | 0 | -1 | 0 | 0 | ||
+ | 7 | Ruedi | -1 | 0 | 0 | 0 | ||
+ | 8 | Peter | 0 | 0 | 0 | 0 | ||
+ | 9 | Hanspeter | 0 | 0 | -1 | -1 | ||
+ | 10 | Hans-Peter | 0 | 0 | -1 | -1 | ||
+ | </code> | ||
+ | |||
+ | ===== rxLookup() ===== | ||
+ | ==== Definition ==== | ||
+ | <code>Result = rxLookup(value [,pattern [,position]])</code> | ||
+ | <code vb>Public Function rxLookup( _ | ||
+ | ByVal iValue As Variant, _ | ||
+ | Optional ByVal iPattern As String = Empty, _ | ||
+ | Optional ByVal iPosition As Long = 0 _ | ||
+ | ) As String</code> | ||
+ | ***iValue** Wert, der geprüft werden soll | ||
+ | ***iPattern** Reg-Exp-Pattern gemäss cRx(): [[vba:cast:cregexp#beispiele_mit_patterns_inkl_den_parametern|cRegExp() Beispiele mit Patterns inkl. den Parametern ]] | ||
+ | ***iPosition** Index des SubMatches des ersten Treffers: rxObject.execute(iPattern)(0).SubMatches(iPosition). Bei -1 wird der geammte Treffer ausgegeben | ||
+ | |||
+ | ==== Beispiele rxLookup() ==== | ||
+ | <code sql>SELECT t.username, rxLookup(t.username, '/(ruedi)/i') AS Ruedi | ||
+ | FROM myTable t | ||
+ | WHERE rxMatch(t.username, '/(ruedi)/i'); | ||
+ | |||
+ | username | Ruedi | ||
+ | -----------|------ | ||
+ | Hansruedi | ruedi | ||
+ | Hans-Ruedi | Ruedi | ||
+ | Ruedi | Ruedi | ||
+ | |||
+ | SELECT t.username, | ||
+ | rxLookup(t.username, '/(ns)?([-\s]?)(ruedi)/i', -1) AS fund, | ||
+ | rxLookup(t.username, '/(ns)?([-\s]?)(ruedi)/i', 0) AS treffer_0, | ||
+ | rxLookup(t.username, '/(ns)?([-\s]?)(ruedi)/i', 1) AS treffer_1, | ||
+ | rxLookup(t.username, '/(ns)?([-\s]?)(ruedi)/i', 2) AS treffer_2 | ||
+ | FROM myTable t | ||
+ | WHERE rxMatch(t.username, '/(ns)?([-\s])?(ruedi)/i'); | ||
+ | |||
+ | username | fund | treffer_0 | treffer_1 | treffer_2 | ||
+ | -----------|----------|-----------|-----------|---------- | ||
+ | Hansruedi | nsruedi | ns | | ruedi | ||
+ | Hans-Ruedi | ns-Ruedi | ns | - | Ruedi | ||
+ | Ruedi | Ruedi | | | Ruedi | ||
+ | </code> | ||
+ | |||
+ | ===== rxReplace() ===== | ||
+ | ==== Definition ==== | ||
+ | <code>Result = rxReplace(value [,pattern [,replace]])</code> | ||
+ | <code vb>Public Function rxReplace( _ | ||
+ | ByVal iValue As Variant, _ | ||
+ | Optional ByVal iPattern As String = Empty, _ | ||
+ | Optional ByVal iReplace As String = Empty _ | ||
+ | ) As String | ||
+ | </code> | ||
+ | ***iValue** Wert, der geprüft werden soll | ||
+ | ***iPattern** Reg-Exp-Pattern gemäss cRx(): [[vba:cast:cregexp#beispiele_mit_patterns_inkl_den_parametern|cRegExp() Beispiele mit Patterns inkl. den Parametern ]] | ||
+ | ***iReplace** Replace-Value geäss RegExp | ||
+ | |||
+ | ==== Beispiele rxReplace() ==== | ||
+ | <code sql>SELECT t.username, rxReplace(t.username, '/(hans)([-\s]?)ruedi/i', '$1$2Peter') AS Ruedi | ||
+ | FROM myTable t | ||
+ | WHERE rxMatch(t.username, '/(ruedi)/i'); | ||
+ | |||
+ | username | Ruedi | ||
+ | -----------|----------- | ||
+ | Hansruedi | HansPeter | ||
+ | Hans-Ruedi | Hans-Peter | ||
+ | Ruedi | Ruedi</code> | ||
===== Code ===== | ===== Code ===== | ||
<source '%%fpath%%/%%fname%%' vb> | <source '%%fpath%%/%%fname%%' vb> |