Excel SQL INSERT Query needs to have strings with & in them

[starl]

Generating SQL Queries to add data to Quickbooks. Fairly standard queries, but some of the strings being added need '&' in the name (eg. Tom & Jerry).
I cannot figure out how - from Excel to a db - I can do this. I'm also using a 3rd party connector for this, so I cannot use the "set define on/off" stuff. I'm limited to making a change to the text in INSERT query that will translate properly when added to the database. For example, using Replace to replace the & with something else that turns into &.

 

[starl]

It looks to me like it's probably more of an XML issue than a SQL one? Have you tried replacing it with &?

Those 5 characters exactly? no. first time I've ever seen such a thing.
Would that be outside the quotes of the specific text string itself?
So if the actual string is:

'(Tom & Jerry) 50lb Bags'

would it then be

'(Tom ' [actually I have no idea how to put a directive like that in here... I can't use & like I would in VBA]  Jerry) 50lb Bags'

researching online, I think you're saying to put it in the string and it might be interpreted the way %20 is used in some web addresses.. so the answer to how:

'(Tom %#38; Jerry) 50lb Bags'

yes?

 

[RoryA]

Yes, though for ease I'd just wrap the string(s) in a Replace function.

 

[starl]

Yes, though for ease I'd just wrap the string(s) in a Replace function.

oh yeah - I have a function that cleans up my strings
I use class modules and functions and all that jazz - makes sharing code a bit difficult.
I class modules!

 

[hydraulicwave]

To insert a string containing an ampersand character & into a database using SQL, you need to escape the ampersand character. This can be done by replacing the & character with '||'&'||'.

For example, the following INSERT statement will insert a string Tom & Jerry into a column name in a table mytable:

INSERT INTO mytable (name) VALUES ('Tom '||'&'||' Jerry');

This works because the || operator concatenates (joins) the strings on either side of it. The single quotes around '&' are needed to treat the & symbol as a plain string character rather than a special SQL symbol.

Alternatively, you can use the CHR() function to insert a special character by its ASCII code. The ASCII code for the & character is 38. So you can use the following INSERT statement to achieve the same result:

INSERT INTO mytable (name) VALUES ('Tom '||CHR(38)||' Jerry');

 

[starl]

INSERT INTO mytable (name) VALUES ('Tom '||'&'||' Jerry');

INSERT INTO mytable (name) VALUES ('Tom '||CHR(38)||' Jerry');

Thanks, I'll add this to the things to try.

 

[starl]

Working solution found! @RoryA brought up the XML link and it appears this is true, though I cannot find mention in the CData VBA help files.
Anyway, the solution is to replace the '&' with &
I didn't have a chance to see if %#38; would also work

Knowing that XML is a part of how they're handling queries definitely helps. Thanks for identifying that.