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



## starl (Dec 28, 2022)

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 &.


----------



## Micron (Dec 28, 2022)

try using Ascii code for ampersand





						ASCII Table - ASCII Character Codes, HTML, Octal, Hex, Decimal
					

Ascii character table - What is ascii - Complete tables including hex, octal, html, decimal conversions




					www.asciitable.com
				



EDIT - as in Chr(38)


----------



## starl (Dec 28, 2022)

I tried that but either it didn't work or I had the syntax wrong (highly possible!)
What's the syntax for using ascii codes in an sql query?
full disclosure.. i'm self-taught on queries. thankfully, what i've had to do has been simple.


----------



## Micron (Dec 28, 2022)

Concatenation. This might not be correct for your situation but along the lines of

"... WHERE myTable.myField = 'Tom " & Chr(38) & " Jerry' AND ..."

Output: ... WHERE myTable.myField = 'Tom & Jerry' AND ...

You should state what sql version this is for. I may be out to lunch if this is for TSql, PostgreSql and the like. I work pretty exclusively with Access sql so that's what my answer is based on. One thing you could do is write a sub, pass your concatenation to a string variable and debug.print the variable to see if it looks right. You should be able to then copy/paste into sql view and see if it runs.


----------



## starl (Jan 2, 2023)

I have no idea what sql version or how to figure that out.. It's Excel to QB through CData. I can't find any info on their site about the version.
I've asked CData what to do - see if they get back to me. Else, I'll experiment with your suggestion next time I connect with the client. 
Thanks!


----------



## Micron (Jan 2, 2023)

Is there a reason why you haven't posted the sql code for anyone to attempt this? Maybe it's confidential?
I guess you can't just import or export. I have no experience with what you're working with & I assume QB means Quickbooks.


----------



## starl (Jan 6, 2023)

It's not typical SQL code - bunch of class modules and functions building the Insert string. And I send it through a 3rd party api that then sends it to QB (yes, QuickBooks).

Here's a string that would generate an error because of the ampersand. I've highlighted the relevant in red. But, I realized after your last message I should contact CData (the 3rd party api). See - I send the sql to their api and I'm betting it breaks things down (notice all the fields, such as <Item Description>) .. though frankly, their function never handled apostrophe's which need special handling.. so I thought I could do the same (that I coded apostrophe's for) for ampersands.. but since it's within the field... I think I'm just confusing myself. oh - I contacted CData and I"m stuck talking to an entry level developer... he's not seeing the issue because he's not experienced with their library and the way they require me to set things up to add to the InvoiceLineItems library. So anyway - I've been hoping if I could learn the rule for ampersands in SQL, I might be able to make the change here. Such as using && would tell sql that it's a string & and not a directive. But Google didn't return anything I could use - everyone talks about using a Set xx Off and I cannot do that.


```
INSERT INTO Invoices (CustomerId, PONumber, Memo, Date, ShipDate, BillingLine1, BillingLine2, BillingLine3, BillingLine4,ShippingLine1, ShippingLine2, ShippingLine3, ShippingLine4, ItemAggregate) VALUES ('800004DE-1428956389', '', '6826', '1/5/2023', '', '5 Star Dairy', '8115- 147th SE', '', '', 'Eulyla Foster 24267 421 Ave Fulton SD 5','','','','<InvoiceLineItems><Row><ItemName>MXS:150 C BROILER- DUCK STARTER</ItemName><ItemDescription>[COLOR=rgb(209, 72, 65)][B](Tom & Jerry) 50lb Bags[/B][/COLOR]</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:150 C BROILER- DUCK STARTER</ItemName><ItemDescription>() 250# Plastic Tubs</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:MISC FORMULA</ItemName><ItemDescription>() 50lb Bags</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row><Row><ItemName>MXS:MISC FORMULA</ItemName><ItemDescription>() 50lb Bags</ItemDescription><ItemQuantity>0</ItemQuantity><ItemOther1></ItemOther1><ItemAmount>0</ItemAmount></Row></InvoiceLineItems>')
```


----------



## Micron (Jan 6, 2023)

Sorry, out of my league. Judging by the <InvoiceLineItems><Row><ItemName> I'd say it's not Access sql.
Good luck!


----------



## starl (Monday at 10:41 AM)

Micron said:


> Sorry, out of my league. Judging by the <InvoiceLineItems><Row><ItemName> I'd say it's not Access sql.
> Good luck!


no, it's not. Was hoping here was some kind of basic sql rule I could use.


----------



## RoryA (Monday at 10:58 AM)

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


----------



## starl (Dec 28, 2022)

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 (Monday at 12:36 PM)

RoryA said:


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


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 (Monday at 12:52 PM)

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


----------



## starl (Monday at 3:15 PM)

RoryA said:


> 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 (Monday at 3:23 PM)

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 (Monday at 3:49 PM)

hydraulicwave said:


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





hydraulicwave said:


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



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


----------



## starl (Yesterday at 7:21 PM)

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 `&amp;`
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.


----------

