Solved Extracting part of string (1 Viewer)

[quest]

Hi,
I have field [Transfer] (text field) like this for example 349/2016 79/2019 i want to get four parts like 349, 2016, 79 and 2019. i got first and can get the last too but how to get 2016 and 79. one more thing if field is only 349/2016 i need only 349 and 2016 so what i will get for part three and four. i used something like this for first part
Part1: Left([Transfer];InStr([Transfer];"/")-1)

 

[tvanstiphout]

I would do a double Split on this: first Split on the space, then Split the resulting items on the forward slash.
Look up the Split function in Help.

 

[quest]

I would do a double Split on this: first Split on the space, then Split the resulting items on the forward slash.
Look up the Split function in Help.

I never used split function before but i think it don't work in query right?

 

[jdraw]

There is an example and discussion Split in a query here.

 

[KitaYama]

but how to get 2016 and 79.

The same way you did for 349 and 2019

SELECT
    Transfer,
    
    Left(Transfer, InStr(Transfer, "/") - 1) AS Part1,
    Mid(Transfer, InStr(Transfer, "/") + 1, InStr(Transfer, " ") - InStr(Transfer, "/") - 1) AS Part2,
    
    Mid(Transfer, InStr(Transfer, " ") + 1, InStrRev(Transfer, "/") - InStr(Transfer, " ") - 1) AS Part3,
    Mid(Transfer, InStrRev(Transfer, "/") + 1) AS Part4

FROM
    YourTableName;

one more thing if field is only 349/2016 i need only 349 and 2016 so what i will get for part three and four.

If your data in the table is not consistent, you can add IIF to above to return a null value.

SELECT
    Transfer,
    
    Left(Transfer, InStr(Transfer, "/") - 1) AS Part1,
    
    Mid(Transfer, InStr(Transfer, "/") + 1,
        IIf(InStr(Transfer, " ") > 0, InStr(Transfer, " ") - InStr(Transfer, "/") - 1, Len(Transfer))) AS Part2,
    
    IIf(InStr(Transfer, " ") > 0,
        Mid(Transfer, InStr(Transfer, " ") + 1, InStrRev(Transfer, "/") - InStr(Transfer, " ") - 1),
        Null) AS Part3,
    
    IIf(InStrRev(Transfer, "/") > InStr(Transfer, " "),
        Mid(Transfer, InStrRev(Transfer, "/") + 1),
        Null) AS Part4

FROM
    YourTableName;

 

[Pat Hartman]

It is always poor practice to store data mushed like this. Also, might there ever be more than 4 parts?