Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Extract From String Expression Needed

Extract From String Expression Needed

Extract From String Expression Needed


I'm trying to come up with an expression to extract a particular section of data from a string (and not having much luck).

Here's the layout of the data contained in the record strings in field1 of my table:

Here's a sample of the records contained in field1 of my table:
72325310-7,77,RETAINER: ASSEMBLY,EA,HONEYWELL L & E (DE PRICED),8531909001,8531900002,9A991.d,,,564.76,,
AA1H37-3,4K,VALVE: CHECK,NEW,EXCH,EA,TEMPEST PLUS MARKETING GROUP, LLC.,8481302090,8481302090,9A991.d,,,1174.88,,500
076308-45714,27,ABRASIVE: DISC,HOOKIT,947A,3IN,NH,120+,BOX,AVIALL DEFAULT VENDOR,6805100000,6805100000,EAR99,,,,,
0200A88BNES,1H,ENGINE: 0200A88B,NEW,EXCH,EA,CONTINENTAL MOTORS, INC.,8407100020,8407100020,9A991.d,,,30723.33,,11000
2157-0167,E4,SLEEVE,EA,ROCKWELL COLLINS, INC.,,,,,,93.99,

I need to be able to extract the last 8 delimited pieces of each record into field2 of my table so that field2 would contain:

I've tried using the STREXTRACT function, but can't seem to make it play correctly. The delimiters (commas) need to remain intact in field2.

Any help would be appreciated!!

RE: Extract From String Expression Needed

Look into the STRECTRACT flags, you can include the delimiters. A simple way to get first and last values with comma as degin and end delimiter is adding commas: ","+alltrim(field)+","

There's another function, that's easier to apply to CSV, as these are, that's ALINES, because you can override the usual behavor to split lines with other separators, simply specify comma. Also no need to add commas as that would just generate an empty start and end element. Of course, when putting together the single AFIELD array elements you need to add in commas again yourself.

Bye, Olaf.

Olaf Doschke Software Engineering

RE: Extract From String Expression Needed


I think there is a comma missing somewhere in the last line.

In this case, SUBSTR(field1, RAT(",", field1, 8) + 1) will fetch the last segment as you require (except in the case of the last record, for the reason above).

RE: Extract From String Expression Needed

If you know that there are always 13 items in each line, then you can use STREXTRACT(), specifying the occurrence.

So to get the 5th item, you would do:

lcItem5 = STREXTRACT(lcField, ",", "," 5)

The 6th item would be:

lcItem6 = STREXTRACT(lcField, ",", "," 6)

And so on.

If you don't know in advance how many items there are in the line, you can find out by using OCCURS():

lnCount = OCCURS(lcField, ",")


Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Extract From String Expression Needed

Or, for a completely different approach, you can copy the items into an array:

ALINES(laArray, lcField, 0, ",")

This will give you an array in which each row corresponds to one of you values. So to get, say, the 8th item from your string, you just need to access laArray(8). And so on.

On balance, I think this would be simpler than my previous suggestion of using STREXTRACT().


Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Extract From String Expression Needed

I'm with atlopes: If you have sufficient commas in all fields the simplest thing is to determine the position of the comma splitting the last 8 expressions from the rest counting commas from right with RAT.

And with any strategy you have problems, if not all fields conform to the necessary amount of commas, also when values themselves contain commas as "CONTINENTAL MOTORS, INC" likely is, that's the death of a simple splitting of values and the reason the normal CSV data structure has strings delimited with quotes to distinguish commas within string delimiters from commas as field separators.

Bye, Olaf.

Olaf Doschke Software Engineering

RE: Extract From String Expression Needed


Excellent! Many thanks to all for the quick input! I ended up using atlopes suggestion...worked like a charm!

RE: Extract From String Expression Needed


I think you missed out a comma


lcItem6 = STREXTRACT(lcField, ",", ",", 6) 


Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close