StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>use a <a href="http://www.regular-expressions.info/" rel="nofollow">regular expression</a>. </p> <p>Regular expressions allow you to do pattern matching on text. It's like String.IndexOf() with wildcard support. </p> <p>For example, suppose your source data looks like this: </p> <pre><code> James Harvey 10939 Madison Whittaker 33893 George Keitel 22982 </code></pre> <p>...and so on.</p> <p>Expressed in English, the pattern each line follows is this: </p> <pre><code>the beginning of the string, followed by a sequence of 1 or more alphabetic characters, followed by a sequence of one or more spaces, followed by a sequence of 1 or more alphabetic characters, followed by a sequence of one or more spaces, followed by a sequence of 5 numeric digits, followed by the end of the string </code></pre> <p>You can express that very precisely and succintly in regex this way: </p> <pre><code>^([A-Za-z]+) +([A-Za-z]+) +([0-9]{5})$ </code></pre> <p>Apply it in VB this way: </p> <pre><code> Dim sourcedata As String = _ "James Harvey 10939" & _ vbcrlf & _ "Madison Whittaker 33893" & _ vbcrlf & _ "George Keitel 22982" Dim regex = "^([A-Za-z]+) +([A-Za-z]+) +([0-9]{5})$" Dim re = New Regex(regex) Dim lineData As String() = sourceData.Split(vbcrlf.ToCharArray(), _ StringSplitOptions.RemoveEmptyEntries ) For i As Integer = 0 To lineData.Length -1 System.Console.WriteLine("'{0}'", lineData(i)) Dim matchResult As Match = re.Match(lineData(i)) System.Console.WriteLine(" zip: {0}", matchResult.Groups(3).ToString()) Next i </code></pre> <p>To get that code to compile, you must import the <code>System.Text.RegularExpressions</code> namespace at the top of your VB module, to get the <code>Regex</code> and <code>Match</code> types.</p> <p>If your input data follows a different pattern, then you will need to adjust your regex. For example if it could be "Chris McElvoy III 29828", then you need to adjust the regex accordingly, to handle the name suffix. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload