using replace function with NULL strings

[cawthor]

I've got an SQL Insert statement that inserts about 100 fields (1 record) into a table. Most of these fields are text, so it's possible they may contain single quotes. These quotes need to be escaped in the insert statement so I was hoping to just use the replace function...eg:

INSERT INTO Tab1 (Field1, Field2...etc) VALUES ('" & replace(field1, "'", "''") & "', '" & replace(field2, "'", "''") & "')"

The problem with this is that some of these fields may be NULL and the replace function blows up on a NULL value (Invalid use of Null: 'replace'). Do I have to do a condition on every field before my statement like:

if not ISNULL(field1) then
field1 = replace(field1, "'", "''")
end if

It just seems a hassle to do this 100 times for each field.

Thanks.

 

[Sheco]

I dunno if this will work but you might try this:
replace((field1 & ""), "'", "''")

I don't think string concat will fail on a null, and if it doesnt, you'll have a proper empty string going into the Replace() function.

 

[Sheco]

I didn't test it... its just an ideer.

 

[steven290]

try this function

function fixsq(sfield)
  tmp=trim("" & sfield)
  tmp=replace(tmp,"'","''")
  fixsq=tmp
end function

INSERT INTO Tab1 (Field1, Field2...etc) VALUES ('" & fixsq(field1) & "', '" & fixsq(field2) & "')"

this will cover both the nulls and the escape quotes

 

[cawthor]

That's perfect...thanks very much!

 

[steven290]

no problem, glad it worked out for you

 

[vongrunt]

Side note: this also replaces NULLs with ''. The difference is meaningless in some 99% of cases, but anyway...

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.
[ba

 

[steven290]

if you ask me all NULLS do is cause problems anyway