Regex pattern for adding N before string value in sql statement

Question

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

Chris · Accepted Answer · 2013-08-07 15:17:21Z

0

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.

edited Aug 7, 2013 at 15:17

answered Aug 6, 2013 at 9:28

Chris

8,6963 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chris Over a year ago

Glad it was what you're after =D

kexdorf Over a year ago

I've found out a problem with the pattern, this example doesn't work: select * from table where id = 'This example does not work, O'Connor says' I change the pattern to (?<!')'[^']+?' and then everything works except if it is a empty string like id = ''

Chris Over a year ago

Hi, I think If you use that approach, you won't be able to match your original examples. I've updated my answer with something that should cover all your cases.

kexdorf Over a year ago

Hi again, I found out with the last one does not work in IN('123', '456') statements. No N will be added at the first value. It seems hard to get all cases to work.

Chris Over a year ago

You could add an or ( to the negative lookahead, e.g. (?<=\s|\()'.*?'

Collectives™ on Stack Overflow

Regex pattern for adding N before string value in sql statement

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related