Professional Web Applications Themes

Covert Functions - Microsoft SQL / MS SQL Server

Hello all, I have a field of a datatype Varchar but has 95% numeric (float) values. I need to get a range of these codes to write a query and I can't seem to figure out how would I go about doing it. My Query is as below. This obviously doesnt not work. select Code from PNCClinicaldb..ctblICD9 where CONVERT(char(5), Code) >= 290.2 and Code <= 319 order By 1 desc Is this even possible? Joshi...

  1. #1

    Default Covert Functions

    Hello all,

    I have a field of a datatype Varchar but has 95% numeric
    (float) values. I need to get a range of these codes to
    write a query and I can't seem to figure out how would I
    go about doing it. My Query is as below. This obviously
    doesnt not work.

    select Code
    from PNCClinicaldb..ctblICD9
    where CONVERT(char(5), Code) >= 290.2 and Code <= 319
    order By 1 desc

    Is this even possible?

    Joshi

    J. Guest

  2. #2

    Default Re: Covert Functions

    Just be aware that not all values that return 1 can be converted... e.g. '.'



    SELECT ISNUMERIC('.')

    -----------
    1

    (1 row(s) affected)



    SELECT CONVERT(FLOAT, '.')

    Server: Msg 8114, Level 16, State 5, Line 3
    Error converting data type varchar to float.



    http://www.aspfaq.com/2390



    "Tom Moreau" <spam.me.cips.ca> wrote in message
    news:phx.gbl...
    You can use the ISNUMERIC() function:

    select Code
    from PNCClinicaldb..ctblICD9
    where CONVERT(float, Code) between 290.2 and 319
    and isnumeric (Code) = 1
    order By 1 desc


    --
    Tom

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


    "J. Joshi" <harvard.edu> wrote in message
    news:03e901c34c96$64c1e560$gbl...
    Hello all,

    I have a field of a datatype Varchar but has 95% numeric
    (float) values. I need to get a range of these codes to
    write a query and I can't seem to figure out how would I
    go about doing it. My Query is as below. This obviously
    doesnt not work.

    select Code
    from PNCClinicaldb..ctblICD9
    where CONVERT(char(5), Code) >= 290.2 and Code <= 319
    order By 1 desc

    Is this even possible?

    Joshi



    Aaron Guest

  3. #3

    Default Re: Covert Functions

    The ISNUMERIC() function tests if the argument is a numeric or not. If so, it returns 1; if not, it returns 0. As Aaron pointed out, it is not a perfect function, however.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "J. Joshi" <harvard.edu> wrote in message news:023401c34ca5$88e8aa50$gbl...
    What is the purpose of identifying them as numeric fields
    on 0 & 1. I think that would be pointless. What I need is
    to give a range based on field values for identifying a
    specific data-set. In the query below - the CODE field is
    a Varchar as it also has alpha-characters. I want to
    ignore those and identify a data-set only from the numeric
    ones.

    Anythoughts of how I would use ISNUMERIC in the query
    below?
     



     
    ----- 
    news:03e901c34c96$64c1e560$gbl... 
    Tom Guest

  4. #4

    Default Re: Covert Functions

    > What is the purpose of identifying them as numeric fields 

    ? ISNUMERIC() returns 1 if the column is numeric.
     

    Yes, exactly how Tom suggested. If you add AND ISNUMERIC(Code)=1 to the
    WHERE clause, you should eliminate any errors because only those that are
    numeric will be returned in the query...


    Aaron Guest

  5. #5

    Default Re: Covert Functions

    > Yes, exactly how Tom suggested. If you add AND ISNUMERIC(Code)=1 to the 

    Again, assuming you don't have any non-convert-able values that fool
    ISNUMERIC.


    Aaron Guest

Similar Threads

  1. Covert Microsoft Office docs to PDF
    By gmeadows@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 1
    Last Post: November 28th, 12:52 PM
  2. Covert 5.0 content or extract for web
    By jtpolk in forum Macromedia Director Basics
    Replies: 2
    Last Post: May 20th, 07:51 AM
  3. Word doc contents covert to pdf
    By Martin_Ashley@adobeforums.com in forum Adobe Acrobat Windows
    Replies: 1
    Last Post: May 2nd, 02:07 AM
  4. How to covert GB2312 to UTF-8 in php?
    By Lian Liming in forum PHP Development
    Replies: 0
    Last Post: February 20th, 03:27 PM
  5. Covert Date to week number
    By Neill Taylor in forum PERL Beginners
    Replies: 12
    Last Post: January 30th, 12:09 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