Isolationg part of an URL

[basicus]

Hi there everybody.

 

I am trying to figure out a way to isolate the main part of an url. I have tried to use all the examples I could find, and almost got it working with a calculation filed and using Left. The problem was that it did not work with domain names containing a number.

 

I'll try to illustrate under what I am trying to achieve:

 

In the following url I need to get out and put into a new field the domain part. I have highlighted it in red:

 

http://www.biogime.no/component/page,shop.browse/category_id,4/option,com_virtuemart/Itemid,100/

 

Everything else I need to drop, so I end up with http://www.biogime.no as the only content of the new field.

 

Any ideas?

[AHunter3]

Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence):

 

 

Middle(

Table::URLfield,

Position(Table::URLfield, "/", 1, 2)+1,

Position(Table::URLfield, "/", 1, 3)-Position(Table::URLfield, "/", 1, 2)-1

)

[basicus]

Thanks a lot! That actually worked. In the meantime I got it to work (sort of) with two temporary fields. I can get rid of those now

[valcore]

Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence):

 

 

Middle(

Table::URLfield,

Position(Table::URLfield, "/", 1, 2)+1,

Position(Table::URLfield, "/", 1, 3)-Position(Table::URLfield, "/", 1, 2)-1

)

Hi FileMaker Guru,

 

I just read your post and I have a similar problem. I have an address field that looks like this: C/O Family Trust*123 Main Street*City CA\

 

I'd like to break out this single field into 4 fields:

address1

address2

city

state

 

Can you help me? I've been trying for hours and I have 38,000 records to fix. Much appreciated.

[AHunter3]

C/O Family Trust*123 Main Street*City CA\

 

 

Middle(Text, Start, Size)

Position(Text, SearchString, Start, Occurrence);

live it, learn it, love it, daydream about it —

 

 

Set Field [TableName::address1, Middle(TableName::UglyField, 1, Position(TableName::UglyField, "*", 1, 1)-1)]

 

Set Field [TableName::address2, Middle(TableName::UglyField, Position(TableName::UglyField, "*", 1, 1)+1, Position(TableName::UglyField, "*", 1, 2) - Position(TableName, "*", 1, 1)-1)]

 

Set Field [TableName::city, Middle(TableName::UglyField; Position(TableName::UglyField; "*"; 1; 2)+1; Position(TableName::UglyField; " "; 1; PatternCount(TableName::UglyField; " ")) -Position(TableName::UglyField; "*"; 1; 2) - 1)]

 

Set Field[TableName::state, Middle(TableName::UglyField, Position(TableName::UglyField, " ", 1, PatternCount(TableName::UglyField, " ")+1, Length(TableName::UglyField))]

 

 

EDIT: formula for city corrected after error pointed out

[valcore]

Thanks for the help. No way I could've figured that out. The script for address1 and address2 worked perfect. However, I can't seem to get the 3rd script (City script) to work at all. What am I doing wrong?

[AHunter3]

Not you, me

 

Typo. Parenthesis in the wrong place. (that'll teach me to freehand them!)

 

Make that:

 

Middle(

TableName::UglyField;

Position(TableName::UglyField; "*"; 1; 2)+1;

Position(TableName::UglyField; " "; 1; PatternCount(TableName::UglyField; " ")) - Position(TableName::UglyField; "*"; 1; 2) - 1

)

 

 

What that long blue clause is doing (the size part of the equation) is get the position of the LAST space in UglyField (because city is differentiated from state by a space not an asterisk) — Position in UglyField of space (" ") starting at beginning, the PatternCount'th occurrence that you come to (i.e., the last one; "as many as there are" always means "the last one");

 

and then subtract from it the starting point of city, which is the position in UglyField of an asterisk, starting from the beginning, the 2nd occurrence, ...minus one (because you don't want either the space or the asterisk so, for length of city, you're one character shorter than the distance between the two delimiters, see?).

[valcore]

My hat is off to you AHunter3. It worked! Thanks for your diligence. smiley_cool

[David Head]

FWIW, here are some alternate calculations. The logic is to turn the data into a value list and then use GetValue function to extract data.

 

address1 =
GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 1 )

address2 =
GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 2 )

city = 
LeftWords( 
   GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ); 
   WordCount ( GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ) ) - 1 
)

state =
RightWords(
 GetValue ( Substitute ( UglyField ; "*" ; "¶" ) ; 3 ); 
 1)

 

See what you make of that