Click here to Skip to main content
15,671,149 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
This is the data that I am reading from a column in database. As below.

10^6 Head Die Size^0.444"~20^6 Head Block Size^N/A~30^12 Head Die Size^0.730"~40^12 Head Block Size^N/A~50^18 Head Die Size^1.006"~60^18 Head Block Size^N/A~70^24 Head Die Size^1.273"~80^24 Head Block Size^N/A

The information starts with a 10 and the numbers increase by 10 before each tilde. 10, ~20, ~30.. and so on. It is also using the caret as a delimiter. I need to separate it into key value pairs and put it into a table format in the UI.

Key: Head Die Size value: 0.444.
Key: Head Block Size value: N/A
Key: Head Die Size value: 0.730
Key: Head Die Size value: 1.006

What I have tried:

This is what I have so far but it is not giving me the correct format.

using System.Text.RegularExpressions;

string pattern = @"\^";
string substitution = "\"";
string input = @"^Rear Payoff Gear Selection^2~20^Rear Payoff Torque^15 psi~30^6 Head Brake Tension^N/A~40^6 Head Block Position^N/A~50^6 Head Bay Orientation^N/A~60^12 Head Brake Tension^25 psi~70^12 Head Block Position^19.3"" (on Touchscreen)~80^12 Head Bay Orientation^(A - 2 Spools); (B - 2 Spools); (C - 2 Spools)~90^18 Head Brake Tension^25~100^18 head Block Position^16.2"" ~110^18 Head Bay Orientation^(A - 4 Spools); (B - 4 Spools); (C - 4 Spools)~120^24 Head Brake Tension^25 psi~130^24 Head Block Position^15.0""~140^24 Head Bay Orientation^(A - 5 Spools); (B - 4 Spools); (C - 5 Spools); (D - 4 Spools)~150^Take-up Gear Selection^3~160^Take-up Torque^40%";
RegexOptions options = RegexOptions.RightToLeft;

Regex regex = new Regex(pattern, options);
string result = regex.Replace(input, substitution);
Updated 26-Aug-22 7:22am
Richard MacCutchan 26-Aug-22 11:24am    
The text that you say you are reading does not match the text in your code sample.
Harrison Mwase 26-Aug-22 11:40am    
Yes, it is all from the same database column. It is in the same format.
Richard MacCutchan 26-Aug-22 12:02pm    
Start by splitting at the "~" marks and then you can separate each item more easily. That leaves you with sets of items in the form:
Number ^ Number Space Title ^ Value
PIEBALDconsult 26-Aug-22 13:23pm    
Is there any chance of having whoever created the text shot?
Richard MacCutchan 27-Aug-22 4:19am    
It's probably the CEO.

1 solution

Generally, it's a bad idea to store "raw data" in a DB, particularly if it holds multiple item values - it's better to process it at source and store is as separate items in a linked table.

But ... try this Regex:

Then it's easy to get KeyValuePairs from it:
string pattern = @"(?:\d+0\^)(.+?)(?:\^)([\d\.]+)";
string input = @"^Rear Payoff Gear ... string from your code ... Torque^40%";
Regex regex = new Regex(pattern);
MatchCollection m = regex.Matches(input);
List<KeyValuePair<string, string>> x = m.Cast<Match>().Select(k => new KeyValuePair<string, string>(k.Groups[1].Value, k.Groups[2].Value)).ToList();
Share this answer
Richard MacCutchan 26-Aug-22 12:51pm    
It seems that your regex is failing to capture the items with "N/A" in the value field.
OriginalGriff 26-Aug-22 13:10pm    
An exercise for the reader! :D
Richard MacCutchan 27-Aug-22 3:58am    
I would have added the extra part myself, but it would have taken too long. And then I had to study the LINQ to understand that statement. Two subjects I still need to work on.
OriginalGriff 27-Aug-22 4:19am    
The Linq stuff is pretty simple, if you just take it left-to-right.
A MatchCollection isn't directly iterable - so you have to Cast it to get anything to work (it predates generics).
Then, you want to Select items from the collection, where "k" is each item in turn.
The KVP just uses the two anonymous groups from the Regex to form a new KVP.
And the iterable result is converted to a List to actually execute it and give a simpler result to debug.

The Regex is a little complex, but basically it's just:
A non capturing group to get the prefix "at least one digit, a zero, and a caret".
A capture group of at least one character, but as few as possible
A non capturing group to find the end of the first text and the beginning of the second: a caret
A final group to capture the second text, which is at least one digit or decimal point.

Have a look at Expresso ( it's free, and examines, tests, and helps construct Regexes.
Richard MacCutchan 27-Aug-22 4:43am    
Thanks, I keep going back to LINQ but every posted solution seems to include something that I still don't know. And I have used RegExr (online helper) for simple regexes, but I will get hold of Expresso.

Your explanations make both items much clearer, diolch yn fawr.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900