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

He is looking only for six digit numbers.

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

does the job

Claudio
 

Pinter Tibor wrote:
> 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
>>
>
> how about "^[0-9]+$"?
>
> t
>