[Solved] Pattern Field for variable text length

by **Maax555** » Mon Apr 16, 2018 11:09 am

Hi, is there a way to use a pattern field for text entry that is variable length. For example in a name field where the entry could be "John", Joe" or Jonathan" for example?
I simply want to ensure all text entered into a field is upper case only. The actual text entered could be a combination of letter and numbers, for example "1234567/A", "ABCDEF/1" or "ABCD123/A1"

thanks

edit this topic's initial post · by **Villeroy** » Mon Apr 16, 2018 3:54 pm

Do you have a set with all possible strings?

by **Maax555** » Mon Apr 16, 2018 4:04 pm

Hi, not sure i follow "Do you have a set with all possible strings?" The data entered could be anything from 5 characters to 30 in any combination of letters or numbers.

edit this topic's initial post · by **Villeroy** » Mon Apr 16, 2018 4:39 pm

If you had a few thousands of these IDs in a table, you could fill a list box with them and choose one by typing into the list box.
With HSQL you can use a field of type VARCHAR_IGNORECASE(length) where upper/lower case makes no difference.
In a query, UPPER("field name") represents the upper case version of the string.

by **Maax555** » Tue Apr 17, 2018 8:41 am

Ah ok, no then. The user will enter the data which will then be saved. The main reason for doing this to be honest is to avoid entering duplicates. I had already used index design to specify unique field but it was allowing the same text if any of the characters were in a different case. So I already had PartNumber Part123/A in the data base it was also allowing an upper/lowercase variation like part123/A or Part123/a.

edit this topic's initial post · by **Villeroy** » Tue Apr 17, 2018 4:34 pm

Simply change the column type from VARCHAR to VARCHAR_IGNORECASE and add an unique index on that column. Now you can not enter any duplicates, no matter if they are upper, lower or mixed case.

by **Maax555** » Thu Apr 19, 2018 4:23 pm

Villeroy wrote:Simply change the column type from VARCHAR to VARCHAR_IGNORECASE and add an unique index on that column. Now you can not enter any duplicates, no matter if they are upper, lower or mixed case.

OMG. I did not even see that field type. I looked through the field types several times over trying to find this and missed it several time.

many thanks as this has been a major headache for me.

[Solved] Pattern Field for variable text length

[Solved] Pattern Field for variable text length

Re: Pattern Field for variable text length

Re: Pattern Field for variable text length

Re: Pattern Field for variable text length

Re: Pattern Field for variable text length

Re: Pattern Field for variable text length

Re: Pattern Field for variable text length

Who is online