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
>
