Professional Web Applications Themes

Character Fields to numbers? - Microsoft SQL / MS SQL Server

Hi All, I have a table that has a field defined as char to allow for both numbers and text to be stored. Is it possible for me to run a select statement that will select out just the records that can be convered to a number format? Example - 1 record may have N/a stored in the cell, 1 record may have 8567 in the cell .. I want to select only the records that are numbers so that they can be converted and used for other things. There are NO cells where numbers and text are mixed - ...

  1. #1

    Default Character Fields to numbers?

    Hi All,

    I have a table that has a field defined as char to allow for both
    numbers and text to be stored. Is it possible for me to run a select
    statement that will select out just the records that can be convered to
    a number format?

    Example - 1 record may have N/a stored in the cell, 1 record may have
    8567 in the cell .. I want to select only the records that are numbers
    so that they can be converted and used for other things. There are NO
    cells where numbers and text are mixed - like "s234tx" .. it's either
    all text or all numbers.

    Hopefully that makes sense. Any help would be appreciated!

    Thanks!
    Bethany Holliday




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Bethany Guest

  2. #2

    Default Re: Character Fields to numbers?

    try IsNumeric(MyField) = -1
    hth
    bob

    "Bethany Holliday" <edu> wrote in message
    news:phx.gbl... 


    BobMcClellan Guest

  3. #3

    Default Re: Character Fields to numbers?

    Try:
    select * from <table>
    where isnumeric(column) = 1 --isnumeric will return for 1 numeric data

    --
    -Vishal
    "Bethany Holliday" <edu> wrote in message
    news:phx.gbl... 


    Vishal Guest

  4. #4

    Default Re: Character Fields to numbers?

    You can use the ISNUMERIC() function

    select
    *
    from
    MyTable
    where
    isnumeric (MyCol) = 1

    Also - if you are using SQL Server 2000 - consider making the column datatype a sql_variant and use SQL_VARIANT_PROPERTY () to test the Base Type.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Bethany Holliday" <edu> wrote in message news:phx.gbl...
    Hi All,

    I have a table that has a field defined as char to allow for both
    numbers and text to be stored. Is it possible for me to run a select
    statement that will select out just the records that can be convered to
    a number format?

    Example - 1 record may have N/a stored in the cell, 1 record may have
    8567 in the cell .. I want to select only the records that are numbers
    so that they can be converted and used for other things. There are NO
    cells where numbers and text are mixed - like "s234tx" .. it's either
    all text or all numbers.

    Hopefully that makes sense. Any help would be appreciated!

    Thanks!
    Bethany Holliday




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

  5. #5

    Default Re: Character Fields to numbers?



    Thanks everyone, the IsNumeric function worked! I'll definitely take a
    look at the sql_variant type for the future.

    Bethany

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Bethany Guest

Similar Threads

  1. InDesign ME Character Problem! Character-Change by Printing or saving *.PS!
    By gree@adobeforums.com in forum Adobe Indesign Windows
    Replies: 48
    Last Post: May 25th, 02:07 PM
  2. Form Fields - '0's not recognized as a character - Arobat 5
    By tom_wojtyszyn@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 3
    Last Post: May 10th, 09:11 PM
  3. limiting character input into editable fields
    By kiaoraFiona webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 4
    Last Post: November 25th, 02:58 PM
  4. #26351 [NEW]: Incorrect handling of Null Fields/Numerical Fields with '0'
    By jabberwocky at ibplanet dot com in forum PHP Development
    Replies: 0
    Last Post: November 21st, 04:47 PM
  5. Numbers not acting like numbers
    By D'Anne in forum PERL Miscellaneous
    Replies: 3
    Last Post: October 2nd, 09:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139