CODEWITHSUNDEEP
regexstring
JimB
JimB

Reputation: 251

Regex and strings, how do I fix a ' in a string?

In reference to this: String issues. Format "First Last" to format "Last, First" with a twist

I have the function working well for mos names. However, I'm stuck on names with a ' in them, ie: O'Shane. I loop though a dataset, pass the name to the function to fix it, then return the fixed name. However, with the string having a ' in it, it messes up the SQL statement.

Here's the relevant code:

 strSql = ("update tbl set Name=" & "'" & strNewName & "'" & " where ID= " & "'" & ID & "'")

strNewName = NameFix(strName)

 Public Function NameFix(ByVal strOriginalName As String) As String
Dim regex As New Regex("(?:(?:mr\.|miss|mrs|ms|by)\s+)?(\S+).*(?<=\s)(\S+)$", RegexOptions.IgnoreCase)
        Dim strNewName As String = regex.Replace(strOriginalName, "$2, $1")
        Return strNewName

how do i get past the ' in a name?

tia!

Upvotes: 0

Views: 48

Answers (2)

Jeffrey Wieder
Jeffrey Wieder

Reputation: 2376

The escape sequence for a ' character is to add another ' so do a replace on ' with ''

Upvotes: 0

Lucas Trzesniewski
Lucas Trzesniewski

Reputation: 51330

Use SQL parameters - do not put user-entered values in an SQL statement ever. This is a big security hole known as SQL injection.

While you could replace every ' with '' to escape the quote, it's still a bad practice. Using SQL parameters will let the server optimize and cache query plans for reuse with different parameters.

Your query would look like this:

UPDATE tbl SET Name = @newName WHERE ID = @id

Then you add the values to the SqlCommand.Parameters collection if you use ADO.NET.

Upvotes: 2

Related Questions