Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CONCAT equivalent in MS Access

I'm doing some work in MS Access and I need to append a prefix to a bunch of fields, I know SQL but it doesn't quite seem to work the same in Access

Basically I need this translated to a command that will work in access:

UPDATE myTable
SET [My Column] = CONCAT ("Prefix ", [My Column]) 
WHERE [Different Column]='someValue';

I've searched up and down and can't seem to find a simple translation.

like image 435
salty Avatar asked Dec 05 '13 15:12

salty


People also ask

What can I use instead of concat?

Use the & Operator Yes, instead of using CONCATENATE, you can use the ampersand operator — & — to combine cell values in Excel.

What is the concatenate symbol in access?

When you want to combine the values in two or more text fields in Access, you create an expression that uses the ampersand (&) operator.

How do you combine two columns in access?

Click the "Arrange" tab, and then click the "Merge" button in the Merge/Split group to merge your selected fields into one.


1 Answers

There are two concatenation operators available in Access: +; and &. They differ in how they deal with Null.

"foo" + Null returns Null

"foo" & Null returns "foo"

So if you want to update Null [My Column] fields to contain "Prefix " afterwards, use ...

SET [My Column] = "Prefix " & [My Column]

But if you prefer to leave it as Null, you could use the + operator instead ...

SET [My Column] = "Prefix " + [My Column]

However, in the second case, you could revise the WHERE clause to ignore rows where [My Column] contains Null.

WHERE [Different Column]='someValue' AND [My Column] Is Not Null
like image 198
HansUp Avatar answered Oct 01 '22 03:10

HansUp