Parse one field's data into multiple fields

[jlicciardi@mssystems.com]

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

Please help

[Darryl Kerkeslager]

Re: Parse one field's data into multiple fields

On 21 Feb 2005 12:51:05 -0800, <jlicciardi@mss ystems.com> wrote:
[color=blue]
> I have a table with a field that has ( a sample )
> 13767;38355;520 270-1;
> 44795;38355;110 818;
> 13981;38355;563 550;
>
> as data. I need to extract the last set of numbers in each row.
> I have tried right$([name],len([name])- instr(1,[name],";")-1)
> but to no avail[/color]

You may not want to use Right() if your field has 'irregular' data like
your first one above. You should use the Mid() function. Does your third
data segment always begin at the 13th position ... or is the best you can
say that it is always after the second semi-colon?

Before you answer that, ask yourself, is there any way to NOT put this
data in the same field to begin with? It looks like it should be in three
separate fields. Second, is there any way to eliminate irregular data,
which would certainly make it easier to extract.


Darryl Kerkeslager

[Darryl Kerkeslager]

Re: Parse one field's data into multiple fields

On 21 Feb 2005 12:51:05 -0800, <jlicciardi@mss ystems.com> wrote:
[color=blue]
> I have a table with a field that has ( a sample )
> 13767;38355;520 270-1;
> 44795;38355;110 818;
> 13981;38355;563 550;
>
> as data. I need to extract the last set of numbers in each row.
> I have tried right$([name],len([name])- instr(1,[name],";")-1)
> but to no avail[/color]

You may not want to use Right() if your field has 'irregular' data like
your first one above. You should use the Mid() function. Does your third
data segment always begin at the 13th position ... or is the best you can
say that it is always after the second semi-colon?

Before you answer that, ask yourself, is there any way to NOT put this
data in the same field to begin with? It looks like it should be in three
separate fields. Second, is there any way to eliminate irregular data,
which would certainly make it easier to extract.


Darryl Kerkeslager

[pietlinden@hotmail.com]

Re: Parse one field's data into multiple fields


jliccia...@mssy stems.com wrote:[color=blue]
> I have a table with a field that has ( a sample )
> 13767;38355;520 270-1;
> 44795;38355;110 818;
> 13981;38355;563 550;
>
> as data. I need to extract the last set of numbers in each row.
> I have tried right$([name],len([name])- instr(1,[name],";")-1)
> but to no avail
>
> Please help[/color]

Hope this doesn't have to be fast... I could do it with strReverse and
then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache. Will this work for ya?

Option Compare Database

Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563 550;
'returns: 563550
' 13767;38355;520 270-1;
'returns: 520270-1
' 44795;38355;110 818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)

End Function


so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, , vbTextCompare))
- 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future
is to not get yourself into it in the first place. Break up your data
as small as you can BEFORE you enter it, and you won't have to do these
things to begin with.

[pietlinden@hotmail.com]

Re: Parse one field's data into multiple fields


jliccia...@mssy stems.com wrote:[color=blue]
> I have a table with a field that has ( a sample )
> 13767;38355;520 270-1;
> 44795;38355;110 818;
> 13981;38355;563 550;
>
> as data. I need to extract the last set of numbers in each row.
> I have tried right$([name],len([name])- instr(1,[name],";")-1)
> but to no avail
>
> Please help[/color]

Hope this doesn't have to be fast... I could do it with strReverse and
then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache. Will this work for ya?

Option Compare Database

Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563 550;
'returns: 563550
' 13767;38355;520 270-1;
'returns: 520270-1
' 44795;38355;110 818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)

End Function


so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, , vbTextCompare))
- 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future
is to not get yourself into it in the first place. Break up your data
as small as you can BEFORE you enter it, and you won't have to do these
things to begin with.

[jlicciardi@mssystems.com]

Re: Parse one field's data into multiple fields

The third data segment doesn't always start at the 13th position,
sometimes there are more than 3 segments. The data is system generated.
There is no way to eliminate the irregular data.
thanks
Your function LastofChunk works fine. Thanks
pietlinden@hotm ail.com wrote:[color=blue]
> jliccia...@mssy stems.com wrote:[color=green]
> > I have a table with a field that has ( a sample )
> > 13767;38355;520 270-1;
> > 44795;38355;110 818;
> > 13981;38355;563 550;
> >
> > as data. I need to extract the last set of numbers in each row.
> > I have tried right$([name],len([name])- instr(1,[name],";")-1)
> > but to no avail
> >
> > Please help[/color]
>
> Hope this doesn't have to be fast... I could do it with strReverse[/color]
and[color=blue]
> then snip off the first semi-colon and then take everything between
> position 1 and the next semi-colon, but that was way too much of a
> headache. Will this work for ya?
>
> Option Compare Database
>
> Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
> strDelim As String) As Variant
> 'Sample inputs and outputs... note, these are TEXT.
> ' 13981;38355;563 550;
> 'returns: 563550
> ' 13767;38355;520 270-1;
> 'returns: 520270-1
> ' 44795;38355;110 818;
> 'returns: 110818
>
> Dim ChunkString As Variant
> ChunkString = Split(strText, strDelim, , vbTextCompare)
> LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)
>
> End Function
>
>
> so I _guess_ you could do it in SQL...
>
> SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, ,[/color]
vbTextCompare))[color=blue]
> - 1)
> FROM...
> WHERE...
>
> but I didn't mess with that... that's the fun you get to have!
>
> BTW, I agree with Darryl - the best way out of this mess in the[/color]
future[color=blue]
> is to not get yourself into it in the first place. Break up your[/color]
data[color=blue]
> as small as you can BEFORE you enter it, and you won't have to do[/color]
these[color=blue]
> things to begin with.[/color]