Extract column values from combine string data

Dear Experts,

I have these common data patterns (City, State Zip) as Location, and I want them to split into 3 accordingly using regex and /or substring combination under VB.Net environment:

The actual data looks like as follows:

Atlanta, GA 30315    
to break into:

Atlanta
GA
30315

Kansas City, KS 66016
to break into:

Kansas City
KS
66016


Thanks








Best solution would be greatly appreciated.


Thanks
JimiJ13I T ConsultantAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi JimiJ13;

As long as you define the pattern string before using the regex this is all that is needed.

Dim match As Match = Regex.Match(Location, pattern)

Location in the statement is the instance of the string to be parsed and match holds the parsed data. That is a one liner.
0
 
Rgonzo1971Connect With a Mentor Commented:
Hi

pls try

strAddress = "Atlanta, GA 30315"

str1 = Split(strAddress, ",")(0)
str2 = Split(Trim(Split(strAddress, ",")(1)), " ")(0)
str3 = Split(Trim(Split(strAddress, ",")(1)), " ")(1)

Open in new window

Regards
0
 
Fernando SotoRetiredCommented:
Hi JimiJ13;

Here is sample code using regular expression to parse the Location string.

' Locations to be parsed
Dim Locations As New List(Of String)() From { "Atlanta, GA 30315", "Kansas City, KS 66016" }
' The regular expression with named groups
Dim pattern As String = "(?<City>[^,]+),\s+(?<State>[^\s]+)\s+(?<Zip>\d+)"

' Get each individual location
For Each Location In Locations
    ' Find the individual locations
    Dim match As Match = Regex.Match(Location, pattern)
    ' Access the three members by using group names
    Console.WriteLine("City = {0}  :  State = {1}  :  Zip = {2}", _
        match.Groups("City").Value, _
        match.Groups("State").Value, _
        match.Groups("Zip").Value )
Next

Open in new window

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
JimiJ13I T ConsultantAuthor Commented:
Hi Rgonzo1971,

Your suggestion works great for a perfect data pattern. However, I see some data with extra spaces between values that resulted to inaccurate outputs.

Any idea how to deal with it?  


Thanks.
0
 
Fernando SotoRetiredCommented:
Can you give an example of, " I see some data with extra spaces between values that resulted to inaccurate outputs. ", so that I may try and adjust the pattern.
0
 
JimiJ13Connect With a Mentor I T ConsultantAuthor Commented:
Hi  FernandoSoto,

Your solution works. Nevertheless, since this will be applied at one data at a time in the Add/Edit page, I just thought that a more specific for a single-line  record method must be more efficient.

What do you think?

Thanks.
0
 
JimiJ13I T ConsultantAuthor Commented:
Hi Fernando,

The unusual pattern that I saw is something like this:

Kansas City  , KS   66016


Thanks.
0
 
Fernando SotoRetiredCommented:
Because the string may not be in the correct format we will reformat the string before using it. Before using the Location variable in the regex do this first and everything else stays the same.

Dim Location As String = Regex.Replace("Kansas City  , KS   66016", "\s{2,},", ",")

Of course you will replace the string "Kansas City  , KS   66016" with the variable of the string needing to be parsed.
0
 
JimiJ13I T ConsultantAuthor Commented:
Hi Fernando,

On the actual implementation of your solution, I got error that says:

"Location is not declared or may be inaccessible due to Protection Level"


Any idea?
0
 
Fernando SotoRetiredCommented:
Hi JimiJ13;

Location is just the variable name I used to represent the data to be parsed, just change it to what you are using to hold the string to be parsed.
0
 
JimiJ13I T ConsultantAuthor Commented:
Fernando has the great solution!
0
All Courses

From novice to tech pro — start learning today.