Link to home
Start Free TrialLog in
Avatar of AidenA
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
SOLUTION
Avatar of Cluskitt
Cluskitt
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AidenA
AidenA

ASKER

ok i'll try that thanks in a few moments... but there's no way you know of to do a whole table at once... either inside sql or inside MS Access interface?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Oops small mistake:

for Oracle.

update column_name set column_name = replace(column_name, '''')  where column_name like '%''%'
Avatar of AidenA

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 = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"

Change to this:

strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%''%'"
Avatar of AidenA

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
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.
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)"
for oracle you can code a procedure to do that for the table and columns the procedure receives as parameters.
Avatar of AidenA

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?
AidenA,

how are you running the Update query??
you may want to use the FInd And replace tool

http://www.rickworld.com/download.html
Avatar of AidenA

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)
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
Avatar of AidenA

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?
<Just from within Access is it?> yes, see my post at http:#a35128221
Avatar of AidenA

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