Split field data into 3 seperate fields

[cmonthetic]

Hi,

Using Access 97.

I have a field in a database that has been populated from unknown means by persons unknown :| over several years.

The issue I have is that this field now needs to be split into 3 separate fields.

The data is in a very rough format of Alpha/Numeric/Alpha, e.g. WAR26A

However the data is not consistent and has been input in a variety of formats.

Following is some examples of the data:

WAR26A
029
WAR35
005B
50L
Also Null fields


The only consistent piece of input is that when the letters have been input at the start of a record they have always put 3 characters in e.g. WAR.

What I need to try and do is get the field split into the following 3 Alpha / 3 Numeric / 1 Alpha, but because there is so much variance in the inoput I am really lost as to how to begin sorting this out.

TIA

 

[fredericofonseca]

You need to loop char by char, test if you have letters or numbers and build each individual field yourself. No automatic way of doing it.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[rac2]

I would do this in steps. Identify patterns that you can specify with WHERE clauses. Define parsing expressions for each pattern. Load the three new fields for each pattern.

For example, one patttern is AAAN...N, 3 alphas with one or more numbers and no trailing alpha. This pattern ends with a number. The WHERE clause would be

SELECT account_id
FROM MyMessyTable
WHERE
            NOT ISNUMERIC( SUBSTRING(1,1,sloppy_column) )
     AND NOT ISNUMERIC( SUBSTRING(2,2,sloppy_column) )
     AND NOT ISNUMERIC( SUBSTRING(3,3,sloppy_column) )
    AND IsNumeric( SUBSTRING(DATALENGTH(sloppy_column),DATALENGTH(sloppy_column),sloppy_column) )

I am sorry, I do not know the Access functions for ISNUMERIC, SUBSTRING, and DATALENGTH. I am confident there are such, but you must find them.

Then use similar functions to parse the sloppy_column and UPDATE the new columns or INSERT rows in your clean table.

UPDATE MyMessyTable SET
             column_A = SUBSTRING(1,3,sloppy_column),
             column_B = SUBSTRING(4,DATALENGTH(sloppy_column),sloppy_column),
             column_C = NULL
WHERE
            NOT ISNUMERIC( SUBSTRING(1,1,sloppy_column) )
     AND NOT ISNUMERIC( SUBSTRING(2,2,sloppy_column) )
     AND NOT ISNUMERIC( SUBSTRING(3,3,sloppy_column) )
    AND IsNumeric( SUBSTRING(DATALENGTH(sloppy_column),DATALENGTH(sloppy_column),sloppy_column) )

Cleaning data takes time and talent. That may be the reason the lazy person who created the mess is gone and forgotten.

 

[earthandfire]

It might be worth investigating the use of Regular Expressions where you could define a pattern of groups consisting of 0 to 3 letters followed by 0 to 3 numbers followed by 0 or 1 letter.

Something like:

^([A-Z]{0,3})(\n{0,3)([A-Z]?)$

You could then use the groups/matches collections to split the data into the individual fields.

You would need to use VBA and import the Scripting runtime.

Hope this helps

 

[cmonthetic]

Thanks for the suggestions.

I have manually updated the fields so that the data is consistent as this seemed the quickest way.

All I need to do now is to split the single field into 3 separate fields.

Any suggestions on the best method on how to achieve this in Access 97?

TIA

 

[PHV]

Take a look at the Left, Mid and Right functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cmonthetic]

Thanks for the repsonses.

I used the left/mid/right commands suggested by PHV in a select statement which populated the new fields.

Easy enough to do once the data had been sorted out in to some sort of consistent pattern!!!!