Solved

easiest way to remove ' character from  a database? in SQL?

Posted on 2011-03-14
20
465 Views
Last Modified: 2012-05-11
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
Comment
Question by:AidenA
  • 7
  • 6
  • 3
  • +3
20 Comments
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 125 total points
ID: 35128178
You can simply update those fields:

UPDATE table
SET field=REPLACE(field,'''','')
WHERE field LIKE '%''%'
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 125 total points
ID: 35128221
in access..
update table
set field=replace(field,chr(39),"")
where instr(field,chr(39))>0
0
 
LVL 6

Assisted Solution

by:akajohn
akajohn earned 125 total points
ID: 35128222
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
 

Author Comment

by:AidenA
ID: 35128271
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
 
LVL 8

Assisted Solution

by:GundogTrainer
GundogTrainer earned 125 total points
ID: 35128284

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
 
LVL 6

Expert Comment

by:akajohn
ID: 35128411
Oops small mistake:

for Oracle.

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

Author Comment

by:AidenA
ID: 35128818
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35128885
strSQL = strSQL + " WHERE [Comments: One Improvement] LIKE '%'%"

Change to this:

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

Author Comment

by:AidenA
ID: 35128947
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
 
LVL 18

Expert Comment

by:Cluskitt
ID: 35128964
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35128970
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
 
LVL 3

Expert Comment

by:CarlsbergFTW
ID: 35128989
for oracle you can code a procedure to do that for the table and columns the procedure receives as parameters.
0
 

Author Comment

by:AidenA
ID: 35129019
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35129039
AidenA,

how are you running the Update query??
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35129050
you may want to use the FInd And replace tool

http://www.rickworld.com/download.html
0
 

Author Comment

by:AidenA
ID: 35129222
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35129344
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
 

Author Comment

by:AidenA
ID: 35129845
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 35130134
<Just from within Access is it?> yes, see my post at http:#a35128221
0
 

Author Comment

by:AidenA
ID: 35136240
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now