January 30, 2014 at 2:31 pm
Is there any way to delete data that contains ' ?
example - desc field is "Bene's"
Trying to delete or update the field gives me these errors -
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string ')
Somehow the app is allowing the data to get in there... Thanks!
January 30, 2014 at 2:51 pm
If you're trying to include a single quotation mark in a string, you have to escape it with another single quote. So your code would look something like:
delete from mytable
where myvar like '%''%'
January 30, 2014 at 2:56 pm
I am still getting the errors... something I'm doing wrong?
January 30, 2014 at 3:04 pm
damcguire (1/30/2014)
I am still getting the errors... something I'm doing wrong?
Can you post the code you are trying to run?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2014 at 3:07 pm
I'm with Sean, we'll need some sample data and code to help you any further.
January 30, 2014 at 3:19 pm
Not sure we need to go so far as full table definitions and sample data. I suspect this is far simpler. OP, please post the update/delete statement and we can probably help you in a matter of minutes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2014 at 6:16 am
actual data in the field is Number of Bene's
among other things, this is the SQL I most recently tried
Delete from rcd_def_dtl
where rcd_prop_desc like '%''%'
errors returned
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string ')
Updating the field only would be okay, but ultimately we would like to delete the whole record.
Thanks
January 31, 2014 at 7:14 am
damcguire (1/31/2014)
actual data in the field is Number of Bene'samong other things, this is the SQL I most recently tried
Delete from rcd_def_dtl
where rcd_prop_desc like '%''%'
errors returned
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 's'.
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string ')
Updating the field only would be okay, but ultimately we would like to delete the whole record.
Thanks
The code you posted will not return that syntax error. There must be something else you are doing.
create table #rcd_def_dtl
(
rcd_prop_desc varchar(30)
)
insert #rcd_def_dtl
select 'Number of Bene''s'
select * from #rcd_def_dtl
where rcd_prop_desc like '%''%'
delete from #rcd_def_dtl
where rcd_prop_desc like '%''%'
select * from #rcd_def_dtl
drop table #rcd_def_dtl
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 31, 2014 at 8:06 am
This syntax works fine to create the table, insert the data, and delete it... but will not work against my actual db field/data. Syntax I used and errors above were copied. Maybe there's somekind of constraint on the db that I'm not aware of. Fwiw, I had asked the software vendor's developers to delete similar data some time ago, and they were not able to. Thanks for trying to help!
January 31, 2014 at 9:01 am
damcguire (1/31/2014)
This syntax works fine to create the table, insert the data, and delete it... but will not work against my actual db field/data. Syntax I used and errors above were copied. Maybe there's somekind of constraint on the db that I'm not aware of. Fwiw, I had asked the software vendor's developers to delete similar data some time ago, and they were not able to. Thanks for trying to help!
So post the ddl to create your table. Since we don't know your datatypes we are guessing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply