Canned Man Canned Man - 19 days ago 5
SQL Question

Add single character to string without using update

Problem



I have around five hundred names in a database that end with an abbreviated name, e.g.:

┌────────────┬───────────┐
│ FNAME │ LNAME │
├────────────┼───────────┤
│ Berthe M │ Bjaaland │
│ Markus M │ Nilsen │
│ Ole T │ Rasmussen │
└────────────┴───────────┘


I would like to use a find and replace to add a full stop after all the single-character names (no Norwegian names consist of single characters, so I know they all are abbreviations). This would be easy if I could run an update query, but I do not have access to that. Is there a way to do this with find and replace or some other workaround?

Desired result



┌────────────┬───────────┐
│ FNAME │ LNAME │
├────────────┼───────────┤
│ Berthe M. │ Bjaaland │
│ Markus M. │ Nilsen │
│ Ole T. │ Rasmussen │
└────────────┴───────────┘


Further usage



Once I get this working, the same method would be applied to changing all names with a single letter in the middle, that is with a syntax like ‘* ? *’.

───

Notes



I am running Access 2007 towards Oracle.

SQL to find names:

select KOMMNR, KRETSNR, BOSTNR, PERSNR,
FORNVN, ETTNVN
from FOLKETELLINGER_KPERSON_1875
where FORNVN like "* ?";


I found I couldn’t use this syntax to run a filter, as it returned anything ending in a character, rather than anything ending in a space and a character. This surprised me, as I have found that many of the dummy characters you can use in
like
queries work well in searches/filters too.

I assume the SQL I would need to run an update, would look something like this:

update FOLKETELLINGER_KPERSON_1875
set FOLKETELLINGER_KPERSON_1875.FORNVN = "* ?."
where FOLKETELLINGER_KPERSON_1875.FORNVN Like "* ?";


I tried copying this syntax to do a single find and replace, but the field was instead changed to ‘* ?.’, not to much surprise.

───

P. S.: I added the sql-update tag, even though I specifically do not want to use it; if it should be removed, please let me know in the comments.

P. P. S.: Drawing an ASCII table was surprisingly calming; now I actually want to get back to work …

Answer

If you don't want to update the data, write a SELECT query to get the desired result.

IIf() to distinguish the cases:

SELECT FORNVN, [FORNVN] & IIf([FORNVN] Like "* ?",".","") AS FORNVN_Dot
FROM etc.