Anoop kumar V <[EMAIL PROTECTED]> wrote on 12/03/2005 11:59:10 AM: > This is very useful. > > Can you please also show how this can be used to validate user input like > for example: > > We have this query currently: > > select id from users where id like '<user_enterred-value>%'; > > Now I want to validate that the user-enterred-value is only a numeric > value... > (the major problem is where users enter special characters that leads to > exceptions / errors being thrown - like if the user enterred ' (a single > quote) that would close the string and the %' would be considered as an > invalid character..) > > Thanks > - Anoop > > On 12/3/05, Gleb Paharenko <[EMAIL PROTECTED]> wrote: > > > > Hello. > > > > > > > > Possible solution is to use REGEXP: > > > > > > > > mysql> select '911' regexp '^[1-9]*$'; > > > > +-------------------------+ > > > > | '911' regexp '^[1-9]*$' | > > > > +-------------------------+ > > > > | 1 | > > > > +-------------------------+ > > > > > > > > mysql> select '91a1' regexp '^[1-9]*$'; > > > > +--------------------------+ > > > > | '91a1' regexp '^[1-9]*$' | > > > > +--------------------------+ > > > > | 0 | > > > > +--------------------------+ > > > > > > > > > > > > > > > > "C.R.Vegelin" <[EMAIL PROTECTED]> wrote: > > > > >Hi everybody, > > > > >I looked for a function to test whether a string is numeric (having > > characters 0..9 only). > > > > >I found in the 5.0 manual Cast() and Convert(), but I don't think these > > do what I need. > > > > >Any suggestion what function to use ? > > > > >Thanks in advance, Cor > > > > > > > > -- > > For technical support contracts, goto https://order.mysql.com/?ref=ensita > > This email is sponsored by Ensita.NET http://www.ensita.net/ > > __ ___ ___ ____ __ > > / |/ /_ __/ __/ __ \/ / Gleb Paharenko > > / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] > > /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET > > <___/ www.mysql.com> > > > > -- > Thanks and best regards, > Anoop
User input is generally validated BEFORE it hits the database. Do it in your application so that you don't need to spend the time for another round-trip to the database to verify if a string is all numeric or not. Most languages have some sort of REGEXP function in them so the same tip should apply no matter what you are using to get the user's input. Once MySQL develops CHECK constraints, then it makes sense to second-check the data at the database level. Until then, just validate your user input at the application level. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
