SELECT WHERE first letter lowercase followed by any upper case letter

[Ron Cates]

I'm using ExecuteSQL() to get a list of fields in a table. What I would like to do is be able to use my naming convention to include or exclude certain fields. For example I name certain utility field starting with a lower case "z" followed by an upper case letter as in Notes::zUtility. So I would like to exclude those fields from the list.

ExecuteSQL (

"
SELECT FieldName FROM FileMaker_Fields
WHERE TableName = ?
AND  FieldName NOT LIKE ?

"

; "" ; ""; "Notes" ; "z%" <-- this is not correct, what would this need to be to specify a lowercase z followed by any upper case letter? )

 

Any help would be greatly appreciated.

Thanks

Edited August 5, 2016 by Ron Cates

[Kevin Frank]

Hi Ron,

Off the top of my head, I'm guessing you'll need to hard code the "AND" portion 26 times, along the lines of...

ExecuteSQL (

"
SELECT FieldName FROM FileMaker_Fields
WHERE TableName = ?
AND  FieldName NOT LIKE ?
AND  FieldName NOT LIKE ?

"

; "" ; ""; "Notes" ; "zA%" ; "zB%" )   <=== keep going with "z%C", etc.

Perhaps someone else can suggest something more efficient?

Kevin

 

 

 

Edited August 5, 2016 by Kevin Frank

[Ron Cates]

Thought that might be the case. What about substituting those fields out after the ExecuteSQL(). would that be any easier? How would that look?

[Kevin Frank]

Substitute doesn't allow wild cards, so I don't see how that would work.

[Ron Cates]

Guess I'll just have to do it the hard way. Lol!

ExecuteSQL ( 

"
SELECT FieldName FROM FileMaker_Fields
WHERE TableName = ?
AND ( 

AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?
AND FieldName NOT LIKE ?

)
" 

; "" ; ""
; "Notes" ; "zA%" ; "zB%" ; "zC%" ; "zD%" ; "zE%" ; "zF%" ; "zG%" ; "zH%" ; "zI%" ; "zJ%" ; "zK%" ; "zL%" ; "zM%" ; "zN%" ; "zO%" ; "zP%" ; "zQ%" ; "zR%" ; "zS%" ; "zT%" ; "zU%" ; "zV%" ; "zW%" ; "zX%" ; "zY%" ; "zZ%" )

 

[Kevin Frank]

I don't find the parentheses to be necessary, but if you're going to use them, don't forget to remove the "AND" from the first "AND FieldName NOT LIKE ?"

(I know you know this, but for the benefit of any newbies who copy your code and wonder why it's not working.)

Edited August 5, 2016 by Kevin Frank

[Ron Cates]

I did catch that. Probably should have edited the post...