Jump to content

SELECT WHERE first letter lowercase followed by any upper case letter


This topic is 2792 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Ron Cates
Link to comment
Share on other sites

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 by Kevin Frank
Link to comment
Share on other sites

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%" )

 

Link to comment
Share on other sites

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 by Kevin Frank
Link to comment
Share on other sites

  • 4 weeks later...

This topic is 2792 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.