From: Claudio Nanni
Date: April 13 2009 7:41pm
Subject: Re: find records with only numbers
List-Archive: http://lists.mysql.com/mysql/217089
Message-Id: <49E39585.5020905@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,
this can be easily solved by using regular expressions.

select field1 as f from table1 t where t.f regexp '[0-9]{6}'

Cheers

Claudio Nanni

Ed Reed wrote:
> I hope someone can give me a suggestion on this.
>  
> I'd like to find records in a table where a specific field only contains a number. 
>  
> For example,
> Select Field1 as f
> from table1 as t
> where lcase(t.f) not like in ('a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z')
>  
> This obviously doesn't work or I wouldn't be asking the question. In this example field1 is a varchar(25) field and it is normally appropriate for it to contain data that has numeric characters and alpha characters. Examples of valid data in this field are '456987','142154','200145C1','954xxx','H 1231','My Test', ......etc. In my query above I'm trying to find the records where there is only a six digit numeric value and no others.
>  
> Thanks for any advice
>
>