I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have single quote and the update fails for that employee record. How can the below command modified to ignore the single quote. I know by using a double single quote can do the job for a single entry and there are many solution for this that has two or three line sql command to achieve the task, please note I am using sqlcmd to update my data and the data is Dynamic, so am looking for a one line command to update the data
Below I have an employee where the CN has value "Testing 'O User", so SQL fails to update. I know I can use a single quote in front of O and that should do the job "Testing ''O User".....But think of Dynamic Values I may not able to go to each record and do that...Need your help please...
sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = 'Testing 'O User' where employeenumber = '12345';"
Below is the error:
Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'O'. Msg 105, Level 15, State 1, Line 6 Unclosed quotation mark after the character string '; '.
sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = REPLACE('Testing 'O User', '''', '''''') where employeenumber = '12345';"
Presumably, you're doing something to place the single quotes around the text value - just modify that to put the fullREPLACE
call around all text values instead. – RDFozz Aug 8 '17 at 15:15