Canned Man Canned Man - 1 month ago 6
SQL Question

How do I remove characters after a specific character number

I have a column with uncertain birth date information, displayed in one ot the following ways (where 0 means digits 0–9):


  • 0000?

  • 0000??

  • 0000!!

  • 0000 ?

  • et sim.



I would like to remove all characters following the first four digits. I have tried using variants of
UPDATE [test] SET BYEAR = left(BYEAR,LEN(FAAR)-4);
, tried swapping it with right, selecting different numbers to remove, but haven’t found a way to specifically tell it to keep the first four numbers and delete the rest.

All help would be much appreciated.

A similar question for Perl, Python have been posted, but I have not found a solution for this in Access/SQL. I am working on a locally stored db, which when linked will be run on an Oracle server.

Answer

In MS Access, you can keep the first four characters by doing:

UPDATE [test]
    SET BYEAR = left(BYEAR, 4);

This does not check that the first four characters are actually digits, but it does do what you want for the data you have provided.

Comments