CODEWITHSUNDEEP
sqloracle-databasems-accessreplace
Canned Man
Canned Man

Reputation: 766

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 …

Upvotes: 1

Views: 98

Answers (2)

Andre
Andre

Reputation: 27634

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.

Upvotes: 4

Deadsheep39
Deadsheep39

Reputation: 611

You cannot set by pattern. Add only one ".". 

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

Upvotes: 0

Related Questions