VB.net/SQL dealing with apostrophes in strings

Hi,
How Do I find the string has an apostrophe, single quote in the string.
I have a function
 Public Function ReplaceSingleQuote(ByRef val As String)
        '//***This function replaces the single quote to double quote***\\
        If IsNothing(val) = False OrElse val <> String.Empty Then
            val = val.Replace("'", "''")
            val = val.TrimStart
        End If
        Return val
    End Function

Open in new window

But, the problem here is that if use for multiple times for the same string it inserts too many single quotes.
Is there any way it just detects an single apostrophe and then replaces it with '' for sql compatibility.
Thanks
RIASAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try
 Public Function ReplaceSingleQuote(ByRef val As String)
        '//***This function replaces the single quote to double quote***\\
        If IsNothing(val) = False OrElse val <> String.Empty Then
            val = val.Replace("''", "'")
            val = val.Replace("'", "''")
            val = val.TrimStart
        End If
        Return val
    End Function

Open in new window

Regards
0
 
RIASAuthor Commented:
Thanks and brb
0
 
Rgonzo1971Connect With a Mentor Commented:
Or you could use regex
Imports System.Text.RegularExpressions
'
      Dim pattern As String = "(')(?:[^'])"
      Dim replacement As String = "$1$1"
      Dim rgx As New Regex(pattern)
      val = rgx.Replace(val, replacement)

Open in new window

REgards
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
RIASAuthor Commented:
Thanks , The problem here is that its gets saved as   '' in sql database .
Is there any way it inserts only one quote
0
 
Rgonzo1971Commented:
Not sure what the problem is
0
 
RIASAuthor Commented:
Also if the string is
str = Count''''''''''''''''''''''''''''ry

Is there a way to return
str = Count''ry

Thanks
0
 
Rgonzo1971Commented:
then try
this regex
Imports System.Text.RegularExpressions
'
      Dim pattern As String = "('')(')+"
      Dim replacement As String = "$1"
      Dim rgx As New Regex(pattern)
      val = rgx.Replace(val, replacement)
      val = val.Replace("'", "''")

Open in new window

0
 
RIASAuthor Commented:
Thanks! Will try and brb.
0
 
RIASAuthor Commented:
I think your first solution is better.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.