Help with an expression for uppercase characters

[Judith]

Re: Help with an expression for uppercase characters

Thank you everyone for your help, I have gone with Jon Moody's single statement filter expression.

[GGandhi]

Re: Help with an expression for uppercase characters

FUNCTION upperWord AS L (name AS C )
	'debug(1)
	dim n as n = 0
	dim position as n = 0
	dim position_old as n = 0
	for i = 1 to len(name) 
		if position > 0 then 
		position_old = position
		end if 
			if asc(substr(name,i,1)) > 64 .AND. asc(substr(name,i,1)) < 91 then
				position = i
				if position = position_old + 1 then
					n = n+1
                                 else
                                 n = 1
				end if
			end if
		    if n = 3 then
                       exit for
                    end if
		next
		if n >= 3 then
			upperWord = .t.
			else
			upperword = .f.
		end if
END FUNCTION

the original code i posted will fail in conditions like
"SUn OIl COrporation" but will work any other combination this corrected code will pick that up too.

[G Gabriel]

Re: Help with an expression for uppercase characters

Or, you could do this (simpler):

x="the abc company"
?regex_match(x,".*[A-Z]+.*")
= .F.

x="the ABC company"
?regex_match(x,".*[A-Z]+.*")
= .T.

x="Smith OIL & GAS Company Limited"
?regex_match(x,".*[A-Z]+.*")
= .T.

x="ABC"
?regex_match(x,".*[A-Z]+.*")
= .T.

[WokingJon]

Re: Help with an expression for uppercase characters

I felt challenged to get this into a single statement and it looks like nw_lower() can do the trick. Just filter the records using:

strequal(nw_lower(fieldname),fieldname,.f.,.t.)=.f.

for example:

test = "The BIG oil company"
?strequal(nw_lower(test),test,.f.,.t.)
= .F.

This compare the 2 lines:

The BIG oil company
The Big oil company

[GGandhi]

Re: Help with an expression for uppercase characters

here is another one:

FUNCTION upperWord AS L (name AS C )
	'debug(1)
	dim n as n = 0
	dim position as n = 0
	dim position_old as n = 0
	for i = 1 to len(name) 
		if position > 0 then 
		position_old = position
		end if 
			if asc(substr(name,i,1)) > 64 .AND. asc(substr(name,i,1)) < 91 then
				position = i
				if position = position_old + 1 then
					n = n+1
				end if
			end if
		
		next
		if n >= 3 then
			upperWord = .t.
			else
			upperword = .f.
		end if
END FUNCTION

[G Gabriel]

Re: Help with an expression for uppercase characters

x="the abc company"
?regex_grep(x,"[A-Z]","$(count)")<>""
= .F.

x="The ABC Company"
?regex_grep(x,"[A-Z]","$(count)")<>""
= .T.

x="Smith OIL & GAS Company Limited"
?regex_grep(x,"[A-Z]","$(count)")<>""
= .T.

[Stan Mathews]

Re: Help with an expression for uppercase characters

The function provided, used in a filter, will limit the record selection to only those records with at least one all capitalized word.

After saving the function to your database (attached here for importing if that's easier for you) the you can use it in a filter like

word_is_upper(your_field_name)

[Judith]

Re: Help with an expression for uppercase characters

That would be great Stan, but in some instances it's correct to have uppercase letters as in ABC Company for instance, I just wanted to be able to query the records to lessen the amount I need to look through to find words like LEE, GAS, OIL, IAN, LOW, DAY

[Stan Mathews]

Re: Help with an expression for uppercase characters

Then again, if all you really want is to normalize the field values so that they consist of only words with initial capitals, just run an update operation. You could add a field and populate it with the new value first if you want to see what would result.

field_value = "Smith OIL & GAS Company Limited"

? w_upper(lower(field_value))
= "Smith Oil & Gas Company Limited"

[Stan Mathews]

Re: Help with an expression for uppercase characters

I think you need a custom function whose result could be used to select records.

FUNCTION word_is_upper AS L (string AS C )
	word_is_upper = .F. 'default return value
	sorted_words = keywords(string)
	wd_list = strtran(sorted_words," ",crlf())
	for each foo in wd_list
		if foo.value == upper(foo.value)
			word_is_upper = .T. 'found a word in all caps
			exit for 'no reason to keep checking
		end if		
	next
END FUNCTION

Modified slightly and replaced the original suggestion to improve the speed. Probably not necessary for smaller amounts of records and short strings but still worthwhile. The keywords() function eliminates any duplicate words. It also sorts any words with at least an initial capital to the start of the list.

[johnkoh]

Re: Help with an expression for uppercase characters

upper("ibm") -> "IBM"

[Judith]

Hi,

I have some data were the field which contains the company name contains uppercase characters (usually words which are 3 characters long) - I am trying to write a query which will find those records (eg Smith OIL & GAS Company Limited).

I'm not having much success as the words could be anywhere within the field, is there an expression which will find uppercase characters within a field.

Thank you

Judith