• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 476
  • Last Modified:

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
0
AidenA
Asked:
AidenA
  • 7
  • 6
  • 3
  • +3
4 Solutions
 
CluskittCommented:
You can simply update those fields:

UPDATE table
SET field=REPLACE(field,'''','')
WHERE field LIKE '%''%'
0
 
Rey Obrero (Capricorn1)Commented:
in access..
update table
set field=replace(field,chr(39),"")
where instr(field,chr(39))>0
0
 
akajohnCommented:
In Oracle

Try this.

update column_name set column_name = replace(column_name, '''')  where column_name like ''''

Check your your results before you commit. Please.

select column_name  where column_name like ''''   should yield zero rows.

A>
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
AidenAAuthor Commented:
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?
0
 
GundogTrainerCommented:

If you test if this displays the expected results:

Select col001,replace ( col001,'''','') from tablename where col001 like '%''%'

Obvously I dont know the actual table or column names.
(If this works you can do an update, the single quote marks can be queried by using 2 single quotes)
0
 
akajohnCommented:
Oops small mistake:

for Oracle.

update column_name set column_name = replace(column_name, '''')  where column_name like '%''%'
0
 
AidenAAuthor Commented:
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)

0
 
CluskittCommented:
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"

Change to this:

strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%''%'"
0
 
AidenAAuthor Commented:
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
0
 
CluskittCommented:
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.
0
 
Rey Obrero (Capricorn1)Commented:
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)"
0
 
CarlsbergFTWCommented:
for oracle you can code a procedure to do that for the table and columns the procedure receives as parameters.
0
 
AidenAAuthor Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
AidenA,

how are you running the Update query??
0
 
Rey Obrero (Capricorn1)Commented:
you may want to use the FInd And replace tool

http://www.rickworld.com/download.html
0
 
AidenAAuthor Commented:
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)
0
 
Rey Obrero (Capricorn1)Commented:
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
0
 
AidenAAuthor Commented:
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?
0
 
Rey Obrero (Capricorn1)Commented:
<Just from within Access is it?> yes, see my post at http:#a35128221
0
 
AidenAAuthor Commented:
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now