replace text that contains comma and quotes

[anski]

i want to replace the text: ,"dummy" with null.
this is my code: stemp = replace(stemp, ",""dummy""", "")
it does not work. what is the correct code to deal with both the comma and the double quote? tia.

 

[MrHans]

You need to perform 2 seperate replacements.
First replace the comma with nothing, then replace the the quotes with nothing.

I would advice to use the Chr() function to do the replacement.

Replace(string,chr(ascii-nr),"")

 

[arnelgp]

Maybe you need some extra quote:


replace(stemp, ","""dummy"""", "")

 

[anski]

thanks for the quick response. i have commas and double quotes in the file that should not be replaced. i need to replace the comma and quotes that are in this format: comma-double quote-text-double quote with null.

 

[MrHans]

You can do it in 1 replacement using the Chr() function.

Replace(string, chr(32) & chr(nr-for-quote), "") and then remove the last character with the mid function for example...

 

[anski]

Maybe you need some extra quote:
replace(stemp, ","""dummy"""", "")

this is the error i get:
Compile error:
Expected: list separator or )

 

[arnelgp]

Create an Update Query:

UPDATE Table1 SET Table1.stemp= Replace([stemp],",""dummy""","");

 

[anski]

Create an Update Query:
UPDATE Table1 SET Table1.stemp= Replace([stemp],",""dummy""","");

i am updating a csv file exported from a union query. i found a workaround for this.
1st replace: (sTemp, """DUMMY""", "DUMMY") --replace with just the word DUMMY with no quotes.
2nd replace: (sTemp, ",DUMMY", "")

 

[isladogs]

I don't understand the issue. This works for me:

SELECT Table3.ID, Table3.Field1, Replace([Field1],",""DUMMY""","") AS New
FROM Table3;

NOTE: I've deliberately included 2 records that don't work.
ID=1 fails as there is no comma; ID=4 fails as there is a space after the comma

Check that neither of these apply in your case

 

[anski]

^You are correct.
Replace([stemp],",""dummy""","") actually works. I used it again and it worked. There must have been a line in my code that did not make it work previously.

 

[arnelgp]

See post #7.