Solved

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

Posted on 2011-03-14
20
471 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35129039
AidenA,

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

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Combo box with two columns question 7 40
office 2016 license quastion 3 45
Access 2010 Report Not Reading Data 19 24
Sql Server Dedupe function 10 10
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

710 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