Oracle and Sql server using different prefix for parameters in parametrized string.
Sql using @p1
ORA using :p1
I would like to use in my SQL just @ and in case that ORA database is used all : character should be replaces with @.
Can you please help me to create regular expression?
Here is some example SQL:
update test_table set text = :p1 where text = 'jana:klara' or some_value = :value or info = 'two'
Similar question and alternative solutions can be found here.
You can use this pattern for your search search:
(?<=\W):(?=\w+)
For instance:
string output = Regex.Replace(input, @"(?<=\W):(?=\w+)", "@");
Here's the meaning of the pattern:
(?<=\W) - The (?<= ... ) syntax declares a positive look-behind. In other words, any match must be preceded by the contents of the look-behind. In this case, it's declaring that matches must be preceded by a non-word character.: - Matches just the colon(?=\w+) - The (?= ... ) syntax declares a positive look-ahead. In other words, any match must be followed by the contents of the look-ahead. In this case, it's declaring that matches must be followed by a one or more word-characters.See the online demo.
I can't think of any reason why the parameter would be the first thing in the input string, but if that were possible, changing it to (?<=^|\W):(?=\w+) would take care of that situation too.
:(?=(?:'[^']*'|[^'\n])*$) ? I think that this is working correctly.Correct regex:
statement = Regex.Replace(statement, @":(?=(?:'[^']*'|[^'\n])*$)", "@");