This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
vba:functions:rxlib [10.10.2016 09:48:42] yaslaw vba:functions:rxmatch renamed to vba:functions:rxlib |
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 | Prüft ob ein Value auf ein Pattern passt. Die RegExp-Objekte werden gecached. Interessant für SQL | ||
Line 13: | Line 13: | ||
{{%%fname%%|Download %%fname%% (V-%%version%%)}} | {{%%fname%%|Download %%fname%% (V-%%version%%)}} | ||
- | ===== Beispiele ===== | + | 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.* | <code sql>SELECT t.* | ||
FROM myTable t | FROM myTable t | ||
Line 35: | Line 47: | ||
SELECT t.*, | SELECT t.*, | ||
- | rxMatch(t.username, '/ruedi/i', 0) AS like_ruedi, | + | rxMatch(t.username, '/ruedi/i') AS like_ruedi, |
- | rxMatch(t.username, '/ueli/i', 1) AS like_ueli, | + | rxMatch(t.username, '/ueli/i') AS like_ueli, |
- | rxMatch(t.username, '/hans/i', 2) AS like_hans, | + | rxMatch(t.username, '/hans/i') AS like_hans, |
- | rxMatch(t.username, '/hans-?(ueli|peter)/i', 3) AS test4 | + | rxMatch(t.username, '/hans-?(ueli|peter)/i') AS test4 |
FROM myTable t; | FROM myTable t; | ||
Line 55: | Line 67: | ||
</code> | </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> |