Trim file extension characters

[Mark Burton]

I'm frustrated I couldn't figure this one out since I have a few Trim functions for counting in from the left, but in this case I want to always Trim the last 4 characters from text in a field.

After I import images I have text in the File Name field, such as:

1-3.jpeg

12-1.jpeg

137-5.jpeg

I want to end up with the trimmed text (below) in a field called Name. Since counting in from the Left won't always be the same, I just want to Trim off the last 4 characters, leaving:

1-3

12-1

137-5

What is the correct Trim function layout to achieve this.

Many thanks. Mark

[Ted S]

Mark,

Try this:

Let (

[

dotCount = PatternCount ( YourTable::YourField; "." );

dotPos = Position ( YourTable::YourField; "."; 1 ; dotCount )

];

Left ( YourTable::YourField; dotPos - 1 )

)

This should remove the file extension no matter how many characters it is.

[Genx]

Well, if you're going to use Let to make it look simpler, you might as well make it simple...

Let (

[

x = YourTable::YourField;

dotCount = PatternCount ( x; "." );

dotPos = Position ( x; "."; 1 ; dotCount )

];

Left ( x; dotPos - 1 )

)

[Genx]

Could also just use:

GetValue( Substitute( YourTable::YourField ; "." ; ¶ ) ; 1 )

Relying on the particular length of a file extension probably isn't a great idea though -- jpeg can also be jpg.

[Ted S]

Simple is good. Simple looking is good too. I guess this makes me a simpleton.

[Ted S]

Genx,

I *may* be wrong here but I think filenames (in Windows) can contain more that one dot. Like this:

filename.more.txt

I haven't seen it often but I believe that I have seen it before. Your second calculation doesn't handle this condition.

[Mark Burton]

OK, that, I would never have figured out! Thank you both so much; these all work great and are exactly what I needed. I've gone with your simplified Let function Genx.

Really appreciate the help.

/Mark

[Genx]

Hi Ted,

Yeah it (Edit: the getvalue() thing) doesn't handle a lot which is why i wouldn't really use it, but it's better to give a few different solutions so the person asking the question can see different ways in which the text functions work.

Mark your thanks should go to Ted, I just made it look a bit simpler, nothing else :o

Edited June 14, 2007 by Guest

[Mark Burton]

Thanks guys. In this case I won't have more than one dot, but this is certainly worth noting and I use that more complex Let function just in case.

Thanks again.

[Genx]

By the way,

Welcome to the forums!! (in case you haven't been welcomed already )

[Mark Burton]

Thanks! I've been reading and searching here for a while - this is my second post. My first received an equally useful reply!!

[Genx]

Ted,

Just to clarify, you are 100% right re the file names and the suggested function, this will do the same thing as the previous let suggestion with the use of about as many functions (okay so maybe i cheat a lil)

Let(

[

x = YourTable::YourField ;

listed = Substitute( x ; "." ; ¶ );

extension = GetValue( listed ; ValueCount(listed))

];

Left( x ; Length(x) - Length(extension) -1 )

)

[Ted S]

Yes sir! As ususal, there is more than one way to skin-the-cat.

[comment]

Right. Like:

Right ( text ; Length ( text ) - 4 )

I want to always Trim the last 4 characters from text in a field.

[Genx]

Yes comment, but look at the question -- trim file extension characters -- not trim last four characters off my text.

If someone asked you "how can i make my database relational using repeating fields" would it be wise to give them the answer to the question they asked?

Can you guarrantee that the only file extension they ever use will be jpeg, or that jpeg will always be jpeg and not jpg?

Edited June 14, 2007 by Guest

[comment]

I can guarantee only that it will always remove the last 4 characters. I thought I would mention it, because it's simple. The potential problems were already covered by previous suggestions.

[Genx]

Okay, as long as anyone reading this in future makes sure they take note of those issues.

[Raybaudi]

For the examples provided, this is a good choise too:

LeftWords ( yourField ; WordCount ( yourField ) - 1 )

[Genx]

Hi Daniele,

This one won't evaluate for me?

Let( x = "test.txt" ;

LeftWords ( x; WordCount ( x ) - 1 )

)

[Raybaudi]

For the examples provided

IOW if a number is always before the last dot

[Genx]

Didn't know that worked, cool trick :o