I need help with the regex pattern to add N before all string values in sql-statements
For example:
Before: SELECT * FROM table WHERE column = '123';
After: SELECT * FROM table WHERE column = N'123';
In that example, I can use this pattern: '[^']+'
. However, I need help with pattern for this example:
Before: SELECT * FROM table WHERE column1 = 'One''Two' AND column2 = 'abc';
After: SELECT * FROM table WHERE column1 = N'One''Two' AND column2 = N'abc';
If there's a double ''
, it should skip those.
Information about my problem: You must precede all Unicode strings with a prefix N when you deal with Unicode string constants in SQL Server
Well, you could use something like this to satisfy the above (provided whatever you're using to apply it supports a negative lookbehind:
(?<!')'[\w]+?'
It's using a negative lookbehind to exclude captures between '
and '
that are preceded by another '
. You may have to adapt the \w
if you required spaces/other characters.
Edit (Updated answer to include your extra strings from the comments):
You could try:
(?<=\s)'.*?'
Which will capture zero or more characters occurring between '
characters (ungreedy), and uses a positive lookbehind to ensure the first '
is preceded by a space character, which should satisfy all the strings you've listed.
You could optionally add a negative lookahead on the end (?!\w)
to skip over the '
in O'Connor.