AidenA
asked on
easiest way to remove ' character from a database? in SQL?
Hi, I have a database, and ' characters (inverted commas) have managed to creep into the database. This is causing problems with my sql queries as I can't run queries which have random ' characters inserted.
So, is there a simple way to just be able to remove all ' characters in the database? Currently it is an MS Access database, but I will have to do the same in an Oracle database also?
Thanks, Aiden
So, is there a simple way to just be able to remove all ' characters in the database? Currently it is an MS Access database, but I will have to do the same in an Oracle database also?
Thanks, Aiden
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops small mistake:
for Oracle.
update column_name set column_name = replace(column_name, '''') where column_name like '%''%'
for Oracle.
update column_name set column_name = replace(column_name, '''') where column_name like '%''%'
ASKER
Just trying the MS Access queries first, but there's syntax errors in both. Managed to fix this one
strSQL = "update [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = Replace([Comments: One Improvement], Chr(39), '')"
strSQL = strSQL + " where (InStr([Comments: One Improvement], Chr(39)) > 0)"
but then i get the error 'Undefined function 'Replace' in expression'
And wasn't able to fix the following one... there's still a syntax error somewhere
strSQL = "UPDATE [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = REPLACE([Comments: One Improvement],''','')"
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"
(but i guess this may also get the same error as above when the syntax error is removed)
strSQL = "update [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = Replace([Comments: One Improvement], Chr(39), '')"
strSQL = strSQL + " where (InStr([Comments: One Improvement], Chr(39)) > 0)"
but then i get the error 'Undefined function 'Replace' in expression'
And wasn't able to fix the following one... there's still a syntax error somewhere
strSQL = "UPDATE [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = REPLACE([Comments: One Improvement],''','')"
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"
(but i guess this may also get the same error as above when the syntax error is removed)
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"
Change to this:
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%''%'"
Change to this:
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%''%'"
ASKER
no that doesn't work: Syntax error in query expression 'REPLACE([Comments: One Improvement],''','') WHERE [Comments: One Improvement] LIKE '%''%'
I'm also getting the impression that you can't use the REPLACE function outside of MS Access. So if that's the case then that's not really what i'm looking for
I'm also getting the impression that you can't use the REPLACE function outside of MS Access. So if that's the case then that's not really what i'm looking for
Depending on your regional settings, you may have to replace , with ;
So, REPLACE([Comments: One Improvement];''';'')
It would help us to provide a better answer if you upload a sample file. If your file contains sensitive data, just replace it with some dummy data that we can test.
So, REPLACE([Comments: One Improvement];''';'')
It would help us to provide a better answer if you upload a sample file. If your file contains sensitive data, just replace it with some dummy data that we can test.
try this
strSQL = "update [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = Replace([Comments: One Improvement], Chr(39), "")"
strSQL = strSQL + " where (InStr([Comments: One Improvement], Chr(39)) > 0)"
strSQL = "update [CSAT Data]"
strSQL = strSQL + " SET [Comments: One Improvement] = Replace([Comments: One Improvement], Chr(39), "")"
strSQL = strSQL + " where (InStr([Comments: One Improvement], Chr(39)) > 0)"
for oracle you can code a procedure to do that for the table and columns the procedure receives as parameters.
ASKER
no that doesn't work: Syntax error in string in query expression 'Replace([Comments: One Improvement], Chr(39), ") where (InStr([Comments: One Improvement], Chr(39)) > 0)'
that's why i changed it from " " to ' '
Are you sure you can use the REPACE function outside of MS Access? From what I've just been looking at on internet it seems you can't... or at least that's what I've read from a few people.
Also, that will be a really slow way of doing it...as I'd have to go through each of about 78 columns doing a replace... would take ages?
that's why i changed it from " " to ' '
Are you sure you can use the REPACE function outside of MS Access? From what I've just been looking at on internet it seems you can't... or at least that's what I've read from a few people.
Also, that will be a really slow way of doing it...as I'd have to go through each of about 78 columns doing a replace... would take ages?
AidenA,
how are you running the Update query??
how are you running the Update query??
ASKER
actually there's already a find and replace in access so no need to install anything.
I'm just running the queries in Visual Studio vb.net code. So just using JET (Microsoft.JET.OLEDB.4.0)
I'm just running the queries in Visual Studio vb.net code. So just using JET (Microsoft.JET.OLEDB.4.0)
it would have been nice if you stated at the start which environment you are using.
click on the request attention and ask to include the vb .net in the zone
click on the request attention and ask to include the vb .net in the zone
ASKER
well it's SQL i'm using... didn't think it mattered where I'm using it from. Guess I thought it would be assumed I was using it outside of Access and Oracle anyway. Not sure, maybe i wasn't clear enough.
Maybe I'm missing something... What was it that you were expect me to be using, out of interest, that your queries above would have worked? Just from within Access is it?
Maybe I'm missing something... What was it that you were expect me to be using, out of interest, that your queries above would have worked? Just from within Access is it?
<Just from within Access is it?> yes, see my post at http:#a35128221
ASKER
ok that wasn't clear. sounded like you were saying 'in access' as opposed to 'in oracle' which just made me think you were taking about running the query on the access database, didn't realise you literally meant do it 'in' access.
Anyway, not sure we're going anywhere here but it did help me clarify a little so i'll just accept now.
thanks for your help, Aiden
Anyway, not sure we're going anywhere here but it did help me clarify a little so i'll just accept now.
thanks for your help, Aiden
ASKER