Expression for Character String

[Judith]

Hi,

I have been trying to write an expression to find a word at the beginning of a field which has two characters, I am going round in circles - and I bet the expression isn't difficult but I am running out of time to complete this work can anyone guide me please.

Regards

Judith

[Judith]

Re: Expression for Character String

By jingo I think I've discovered the expression, just shows what you can do when you've had a good nights sleep

len(word(Company_Name,1))=2

Judith

NOW I NEED HELP IN CONVERTING THIS FIRST WORD INTO UPPER CASE WITH THE REMAINDER OF THE FIELD IN LOWER CASE - I ATTEMPTED THIS AND ENDED UP WITH JUST THE FIRST TWO CHARACTERS CONVERTED TO UPPER CASE AND LOSING THE REST OF THE FIELD.

[Tom Cone Jr]

Re: Expression for Character String

How about showing us some sample entries, before and after they're pulled apart ?

[Judith]

Re: Expression for Character String

Hi Tom,

It is mainly were a company has an abbreviated name.

E.G. Exl Holdings Limited should be EXL Holdings Limited
Emi Group should be EMI Group.

Thanks

Judith

[Tom Cone Jr]

Re: Expression for Character String

But your example is using 3 letters, not 2. ???

Your filter expression would never "find" these entries.

You basically have 3 things going on in this thread.

a) how to query the table and "find" specific records; and

b) how to parse them into two or more "words"; and

c) how to shift the first "word" to all caps, without changing the others.

So, where, exactly, are you stuck?

[Judith]

Re: Expression for Character String

Hi Tom,

Sorry I am trying to do too many things at once - and confusing everyone else into the bargain. I have managed to work out an expression for finding records that have a two or three character word at the beginning of the field which is fine, but now I want to change this first word to UPPER CASE with the remainder of the field staying as it is.

So that my example Exl Holdings Limited becomes EXL Holdings Limited
I have approx 500 records that need to be corrected and don't want to change them one by one - hope you can help.

Best regards

Judith

[Tom Cone Jr]

Re: Expression for Character String

So, you're planning a filtered update operation to change all the 2 letter "first words" to upper case, and then a second filtered update operation to change all the 3 letter "first words" to upper case? That seems reasonable.

In my personal experience batch update operations should never be run until at least two good backups have been made first.

You have the filter running, and you know how to make the first word upper case. So where are you stuck?

[Tom Cone Jr]

Re: Expression for Character String

Maybe this will help.

Make your backups before experimenting!

My_field = "exl holdings, ltd."

? upper(word(my_field,1))       ' illustrates how to force first word upper case
= "EXL"

? at(" ", my_field,1)       'the at() function returns the position of a character in the string; read the helps for details
= 4

? substr(my_field, 4, len(my_field) - (4 -1))  ' substr() function extracts a sub string from the string; read the helps for details
= " holdings, ltd."

? substr(my_field, at(" ", my_field, 1), len(my_field) - (at(" ", my_field,1)))   ' use at() in the substr() expression to generalize
= " holdings, ltd"

? upper(word(my_field, 1)) + substr(my_field, at(" ", my_field, 1), len(my_field) - (at(" ", my_field,1)))  ' put it together
= "EXL holdings, ltd"

Using the final expression, Alpha is being told to:

a) find the "first" word in the my_field string, and then force it upper case; then
b) concatenate the "first" word with a character string composed of a sub string extracted from my_field string according to the following rules:
- begin "at" the location of the first blank space;
- continue for 'x' number of spaces, where 'x' is the length of the my_field string MINUS the "at" position of the first blank space.

Please don't use this in your app until you understand what's going on. Grab the help file documentation and read up on any functions being used here you don't know like the back of your hand. This sort of thing is bread and butter functionality for spreadsheet and database folks. Might be a good learning experience for you and others visiting this thread later on, so ask questions if you have 'em.

[Judith]

Re: Expression for Character String

Thanks Tom, I will make a couple of backups of my database up as suggested and then read and digest (hopefully!!) your very helpful instructions. I have used the AT function and word function before but not together. I will report back later. Once again, thank you for your help.