Post reply

Looking for SQL Built-in rule to permanently replace a character with another in a string.

  • December 8, 2016 at 3:27 am

    #338137

    When working with a many text fields (Free text) one always has to replace the apostrophe char with multiple single quote characters in order not to accidentally generate termination of SQL syntax errors. Personally, I use the inverted apostrophe "`" charater.

    The problem I have is that my whole application needs to permanently replace all the free text values - or at least test each one of them to overcome the termination error that SQL generates if it is present. Every text field on every form in every application.

    I would like to know if there could be a configuration in SQL that states it needs to do this permanently, so that this replace is done on the server and not on every PC / laptop / web server ? And perhaps there would be an override switch to skip the rule in a certain update as well ?

    Any ideas ?

  • December 8, 2016 at 5:45 am

    #1916738

    There isn't a blanket "replace all", no. However, with a single quotation, your problem would still arise regardless.

    If you want to blanket replace ' with `, your SQL statement would still need to be :

    REPLACE([text Field],'''','`')

    Not double quoting isn;'t going to work.

    My query, however,actually makes me wonder how you are communicating with the SQL Server, and it smells a little like your using direct SQL, rather than parametrised.

    For example, if we were in VB, and you wanted to pass someone's name, which was "Mr John M'Keys". To pass the ' in SQL you would have to double quote it. This would mean you might have code such as:

    Dim strName as String

    strName = txtName 'Get the value from an on screen field

    'Need to check for that single quote

    strName = REPLACE(strName, "'", "''")

    sSQL = "exec Sales.dbo.GetCustomers_sp @Name = " & strName & ";"

    sConn = ConfigurationManager.ConnectionStrings("Sales").ConnectionString

    oComm = New System.Data.SqlClient.SqlConnection(sConn)

    oComm.Open()

    I had to tell VB to update the passed parameter.

    Now let's go with paramterised version:

    Dim strName as String

    strName = txtName 'Get the value from an on screen field

    sConn = ConfigurationManager.ConnectionStrings("Sales").ConnectionString

    oConn = New System.Data.SqlClient.SqlConnection(sConn)

    oConn.Open()

    sSQL = "dbo.GetCustomers_sp"

    oComm = New System.Data.SqlClient.SqlCommand(sSQL, oConn)

    oComm.CommandType = CommandType.StoredProcedure

    oComm.Parameters.Add(New SqlParameter("@Name", strName))

    Here I don't need to tell VB to add double quotes, as it's all handled for me. The SQL connection manager is clever enough to know that a string containing a ' needs to be double quoted.

    Unfortunately, although I hate to say it, it's sounds like the problem is not your SQL back end not like single quotes, but that the front end hasn't been written to cater for characters that require escaping.

    P.s. Personally, I really don't like the solution of using a `instead of a '. They aren't the same character. If you were therefore searching for Mr John M'Key, you'd have to either get your staff to instead type "Mr John M`Key" or change it in your SP for searching. it creates more problems than it solves in my opinion, when the issue can easily be handled at application level.

    Edit: Couple of typoes. I've probably still missed some as well.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • December 8, 2016 at 8:24 am

    #1916801

    Most of the time when dealing with single-quote string issues (such as dynamic SQL) I love setting quoted_identifier off and then using double-quotes around the string and single-quotes inside. Never have any problems then.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • December 12, 2016 at 2:36 am

    #1917319

    Thanks Thom

    I was hoping there could be a preprocessor method that would remove certain character before SQL engine started processing the string.

    But I understand that the Parameter object would do this for me as you described. I can add this in the data class I am using.

    Thanks for your input

    William.

  • December 12, 2016 at 2:38 am

    #1917321

    Great Kevin.

    Thanks. I will try this.

    Cheers

    William

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply