List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:April 13 2009 7:41pm
Subject:Re: find records with only numbers
View as plain text  
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
>
>   

Thread
MySQL runs on 16-cores serverMoon's Father10 Apr
  • Re: MySQL runs on 16-cores serverUwe Kiewel10 Apr
    • Re: MySQL runs on 16-cores serverWm Mussatto10 Apr
      • Re: MySQL runs on 16-cores serverAndrew Garner10 Apr
    • Re: MySQL runs on 16-cores serverMoon's Father11 Apr
      • Re: MySQL runs on 16-cores servermos11 Apr
      • Re: MySQL runs on 16-cores serverUwe Kiewel11 Apr
  • Re: MySQL runs on 16-cores servermos10 Apr
    • Re: MySQL runs on 16-cores serverAndy Smith11 Apr
      • Re: MySQL runs on 16-cores serverBaron Schwartz11 Apr
        • Re: Re: MySQL runs on 16-cores serverKen Jacobs11 Apr
        • Re: MySQL runs on 16-cores serverDavid Sparks14 Apr
          • Re: MySQL runs on 16-cores serverBaron Schwartz14 Apr
      • Re: MySQL runs on 16-cores servermos11 Apr
        • RE: MySQL runs on 16-cores serverJerry Schwartz13 Apr
          • RE: MySQL runs on 16-cores servermos13 Apr
            • Re: MySQL runs on 16-cores serverBaron Schwartz13 Apr
              • Re: MySQL runs on 16-cores servermos14 Apr
                • Re: MySQL runs on 16-cores serverBaron Schwartz14 Apr
                  • RE: MySQL runs on 16-cores serverMark14 Apr
            • find records with only numbersEd Reed13 Apr
              • Re: find records with only numbersClaudio Nanni13 Apr
              • Re: find records with only numbersPinter Tibor13 Apr
                • Re: find records with only numbersClaudio Nanni13 Apr
            • RE: MySQL runs on 16-cores serverWm Mussatto13 Apr
    • Re: MySQL runs on 16-cores serverMoon's Father16 Apr
  • RE: MySQL runs on 16-cores servergenie.japo15 Apr