How do I remove characters after a specific character number

Question

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.

It's not entirely clear to me why `left()` is not appropriate or why you need `len()`. Do you mean that the four first digits may not be at the start of the string? E.g. `'Foo 0879 Bar!!'`? — Álvaro González, Nov 04 '16 at 09:35
Please give better (more varied) sample data in your question(s). As Gordon wrote, for your examples `Left(BYEAR, 4)` would suffice. — Andre, Nov 04 '16 at 10:25
@ÁlvaroGonzález I have tried making it clear now that the 0s represented digits and only digits, i.e. *not* `*0000*`, always `0000*`. — Canned Man, Nov 04 '16 at 15:06
I have tried updating my answer to clarify as per your comments. Please advice if more information is needed; please remove downvotes if it is now clear what I am asking. Also, I am not sure having the Oracle tag is appropriate, as I am working locally at the time being. — Canned Man, Nov 04 '16 at 15:08
@André I have provided all the data necessary, actually. But a non-minimum reprex sample could be provided if necessary. — Canned Man, Nov 04 '16 at 15:41
@ÁlvaroGonzález As to the `len()` question: I don’t know; it was a solution I found via another SO question, which worked for some of the things I was trying to do. — Canned Man, Nov 04 '16 at 15:43

score 3 · Accepted Answer · answered Nov 04 '16 at 09:33

3

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.

answered Nov 04 '16 at 09:33

Gordon Linoff

1,242,037
58
646
786

Is there any way to make it check for numbers first? It shouldn’t be necessary, but for later usage, it would be nice to know how to. – Canned Man Nov 04 '16 at 15:08
1

@CannedMan . . . It is possible to do, but not in a way that works in both MS Access and Oracle. – Gordon Linoff Nov 05 '16 at 01:00
If there is a pure SQL way of doing it, that would have been a nice addition to your answer, but I’m assuming, as per your reply—being that such a solution would not work both in MSA and Oracle—that there isn’t. – Canned Man Nov 07 '16 at 10:06
@CannedMan . . . The overlap between MS Access and Oracle is many things, but I would not call it "pure SQL". – Gordon Linoff Nov 08 '16 at 02:19

Aleksej · Answer 2 · 2016-11-04T11:42:54.650

In Oracle, you can try the following:

WITH TEST(T) AS
(
    SELECT 'xx0000?' FROM DUAL UNION ALL
    SELECT '0000???' FROM DUAL UNION ALL
    SELECT '0000  ?' FROM DUAL UNION ALL
    SELECT 'xx00009' FROM DUAL UNION ALL
    SELECT '000099?' FROM DUAL
)
SELECT REGEXP_SUBSTR(T, '[0-9]{4}')
FROM TEST

REGEXP_SUBSTR(T,'[0-9]{4}')
----------------------------
0000
0000
0000
0000
0000

In your situation:

update yourTable set yourField = REGEXP_SUBSTR(yourField , '[0-9]{4}')

How do I remove characters after a specific character number

2 Answers2