CODEWITHSUNDEEP
sqlms-access
vuyy1182
vuyy1182

Reputation: 1676

How to append a letter in column values access sql

I have a table

 Name     Description       EID  

 name1      ssdad          1001
 name2      gfd            1002
 name3      gfdsffsdf      1003
 name4      ssdad          1004
 name5      gfd            1005
 name6      gfdsffsdf      1006

i want to append C letter for EID's 1001,1002,1003 only. How to specify that in sql query. Like C1001,C1002,C1003.

 Name     Description       EID  

 name1      ssdad          C1001
 name2      gfd            C1002
 name3      gfdsffsdf      C1003
 name4      ssdad          1004
 name5      gfd            1005
 name6      gfdsffsdf      1006

Upvotes: 1

Views: 1089

Answers (3)

HansUp
HansUp

Reputation: 97100

Since you're targeting only 3 values, you could include them in a list.

SELECT
    EID,
    IIf(EID In ('1001','1002','1003'), 'C', '') & EID AS adjusted_EID
FROM YourTable;

If you actually want to change the stored values, you can use that list in the WHERE clause of an UPDATE.

UPDATE YourTable
SET EID = 'C' & EID
WHERE EID In ('1001','1002','1003');

If you had many more target values, such a list would become unwieldy. In that case, you might prefer a different approach to check whether the current value is one of your target values.

Upvotes: 2

shree.pat18
shree.pat18

Reputation: 21757

You can use IIF to check a condition and then do something accordingly. In your case, you can check if EID is less than 1004, and append 'C' to the result if it is. So if you want to only select, query will be:

select name, description, IIF(val(EID) < 1004, "C" & EID, EID) 
from yourtable

If you are updating just change it to

update yourtable 
set EID = "C"& EID 
where val(EID) < 1004

Upvotes: 2

Smandoli
Smandoli

Reputation: 7019

UPDATE table SET EID = "C" & [EID]
WHERE (Mid([EID],4,1)="1"
   OR Mid([EID],4,1)="2"
   OR Mid([EID],4,1)="3");

Upvotes: 1

Related Questions