1

hoping someone can help me as i've realisticaly sunk 12+ hours into trying to solve this problem -

for some odd reason a comma separated string list value within a JSON string is being deserialised from

'High','Low','Medium'

to the value in the an object as being:

'''High'',''Low'',''Medium'''

All these extra single quotes seem to break things, so thought about somehow removing them via a regex pattern, possibly splitting into a list further and joining them with the correct amount of single quotes (one either side of a value, as it is on the incoming json string).

I thought I Would try my hand at simply extracting the values between a matching set of quotes and I had found a pattern that worked, but later found it did not (see the commented out part) and the second pattern I have there seemed to work on regex101 but I think that is just highlighting all the single quote pairs

//parameter.Value = Regex.Replace(parameter.Value, "/('+')/g", "'");
string[] values = Regex.Split(parameter.Value, @"/(?<![a-zA-Z])'|'(?![a-zA-Z])/g");
    
for (int j = 0; j < values.Length; j++)
    values[j] = string.Format("'{0}'", values[j]);

parameter.Value = string.Join(",", values);

I was hoping someone would be able to help me out with pointing out where I may be going wrong in my pattern? I really have tried but that comma separated list could contain comma's and special characters in themselves (including apostrophe's) making it that bit harder.

Any help or guidance appreciated

Update

Thanks to @Wiktor Stribiżew for pointing out an obvious lapse in thought to me, problem is resolved. Appeared to be using wrong method along with string literal's in the pattern.

Other very reasonable options in the replies, so thanks to them too.

bry96
  • 23
  • 4
  • Do not use regex literals inside string literals. Does `@"(?<![a-zA-Z])'|'(?![a-zA-Z])"` work for you? – Wiktor Stribiżew Jun 25 '21 at 09:37
  • Are you sure this is indeed how it is deserialized and indeed why things break? You may be seeing the extra quotes because a tool [shows](https://stackoverflow.com/a/5466227/11683) you them (because e.g. it is a context of an SQL literal)? It would seem from your code that you are [actually](https://meta.stackexchange.com/q/66377/147640) trying to pass a chunk of SQL for the `IN()` clause as a parameter; that is [not going to work](https://stackoverflow.com/q/337704/11683). – GSerg Jun 25 '21 at 09:43
  • 1
    Perhaps you can match all the single quotes that you want to remove `(?<=(?:^|,)'+)'|'+(?='(?:,|$))` https://regex101.com/r/Dx3v3p/1 – The fourth bird Jun 25 '21 at 09:44
  • @WiktorStribiżew so it does, however, it would then produce this: ```string[15] { "", "", "", "Medium", "", ",", "", "Low", "", ",", "", "dgufdsghuf65465sdgh'sd99.mjhjmf", "", "", "" }``` – bry96 Jun 25 '21 at 09:45
  • 1
    Do not use `Regex.Split`, use `Regex.Replace(text, @"(?<![a-zA-Z])'|'(?![a-zA-Z])").Split(',')` – Wiktor Stribiżew Jun 25 '21 at 09:47
  • Please update the question as now, all it says, is that your copied regex from regex101 does not work in C# code. Please use the correct regex and state the problem clearly providing the expected output. – Wiktor Stribiżew Jun 25 '21 at 10:20
  • Thanks for the comment @GSerg, quite insightful. Unfortunately it's not down to the tool (VS Debugger) and the value is indeed as listed. (can ensure thanks to an sql trace). There is some magic in our code somewhere that splits this list further out into the relevant SQL ```IN()``` clause (good guess btw). Could go and update that code, but unaware of consequences let alone where it resides due to rather large system and feel as if though i'd still be facing the same problem. Again, thank you – bry96 Jun 25 '21 at 10:22
  • 1
    Maybe something like https://ideone.com/bgtyOl – The fourth bird Jun 25 '21 at 10:24
  • 1
    `can ensure thanks to an sql trace` - there are supposed to be quotes in the sql trace, otherwise it would be invalid SQL. Which would indicate the "code somewhere that splits this list further out into the relevant SQL" does not actually do that, or does it wrongly. I still don't see how the single quotes could have doubled as a result of deserializing json. – GSerg Jun 25 '21 at 10:26
  • Do not edit the question saying it is resolved. Provide feedback here as a comment what worked for you to let the user who helped you post a full fledged answer and get the hard-earned reputation, or just remove the question if it is that unclear. – Wiktor Stribiżew Jun 25 '21 at 10:59
  • @GSerg I meant more that due to the extra single quotes enclosing the entire string (beginning and end have 3, its 2 elsewhere) its being misinterpreted by the method building the query clearly as that string is being inserted as one long parameter instead of each value getting its own. ie when enclosed in single quotes ```@XFilter_5_X0=N'Medium',@XFilter_5_X1=N'Low',@XFilter_5_X2=N'High'``` vs straight out of the value from the deserialize ```@XFilter_5_X0=N'''Medium'',''Low'',''High'''```. The Regex Quickfix has resolved this issue for me and is working now – bry96 Jun 25 '21 at 11:21
  • @WiktorStribiżew Your answer worked and is the solution I am using, if you submit it, I will mark it as accepted. Sorry for being a little slow, New to posting on SO and dont see the option of marking an answer only post my own. Will keep your comments in mind for the future – bry96 Jun 25 '21 at 11:25

1 Answers1

1

Taking into account your data format, namely, no commas inside the fields, you can use

var result = Regex.Replace(text, @"(?<![a-zA-Z])'|'(?![a-zA-Z])", "").Split(',');

The point is that Regex.Split "breaks" the string with the matches, while Regex.Replace will replace all matches (here, I am using an empty string, so it will remove the matches) and then .Split(',') will break the result on commas.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563