none
sql 2008 convert nvarchar to float

    Question

  • Hi
    i need to write a condition in my query where my column value is greater than 2
    my column name is sales..and when ever the value in sales in greater then 2 i should get  the O/P
    but datatype of my  column sales is "nvarchar" 

    i have used the condition
    cast(isnull(sales,0) as float)>2,

    but this didnt work.
    if some one could help me ...

    • Moved by Tom PhillipsModerator Monday, September 13, 2010 5:50 PM TSQL Question (From:SQL Server Database Engine)
    Monday, September 13, 2010 7:27 AM

Answers

  • select

     

    c

    from

     

    #t

     

    WHERE CASE WHEN c LIKE '%[^0-9.]%' THEN 0

     

    ELSE 1 END = 1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by MS308 Monday, September 13, 2010 9:32 AM
    Monday, September 13, 2010 9:11 AM
    Answerer

All replies

  • When you saus that it did not work, what do you mean? Error? Wrong result?

    create

     

    table #t (c nvarchar(20))

    insert

     

    into #t values ('1')

    insert

     

    into #t values ('2.3')

    insert

     

    into #t values ('0.5')

    insert

     

    into #t values ('6.2')

    insert

     

    into #t values ('3')

    select

     

    * from #t

    where

     

    cast(c as decimal(18,3))>2


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 13, 2010 8:10 AM
    Answerer
  • When you saus that it did not work, what do you mean? Error? Wrong result?

    create

     

    table #t (c nvarchar(20))

    insert

     

    into #t values ('1')

    insert

     

    into #t values ('2.3')

    insert

     

    into #t values ('0.5')

    insert

     

    into #t values ('6.2')

    insert

     

    into #t values ('3')

    select

     

    * from #t

    where

     

    cast(c as decimal(18,3))>2


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 13, 2010 8:10 AM
    Answerer
  • HI
    i get the following error when i execute
    cast(isnull(sales,0) as float)>2


    "Error converting data type nvarchar to float."

     

    Monday, September 13, 2010 8:13 AM
  • i have a table
    DIM_LensAttributeMeasure with column MeasureName (nvarchar(50)),DataType(nvarchar(50))

    i have a table FACT_AttributeMeasure with column Value(nvarchar(50))


    so i have written a qurey in sql 2008

    select DataType, MeasureName, value from DIM_LensAttributeMeasure a
    inner join FACT_AttributeMeasure b on a.SkeyMeasureID=b.SkeyMeasureID
    where  a.MeasureName='length'
    and cast(Value as decimal(18,3))>2
    or cast(Value as float)>2

     

    but in both the case i get error as "Error converting data type nvarchar to numeric" or "Error converting data type nvarchar to float" respectively

    Monday, September 13, 2010 8:23 AM
  • hey there,

    you can use the convert function: CONVERT(float,sales) as sales

    but if you want to add a condition when >2 you should also specify what value to return if < 2, like returning a NULL value

    it would be like this: CASE WHEN sales > 2 THEN CONVERT(float,sales) ELSE NULL END AS sales

    Monday, September 13, 2010 8:29 AM
  • HI thanx for reply
    for the case of null i have used 


    cast(isnull(sales,0) as decimal(18,3))>2
    convert(float,isnull(sales,0)) 

     because i wont only the data greater than 2..but still it throws the error same above error of dataconversion

    Monday, September 13, 2010 8:51 AM
  • select

     

    c

    from

     

    #t

     

    WHERE CASE WHEN c LIKE '%[^0-9.]%' THEN 0

     

    ELSE 1 END = 1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by MS308 Monday, September 13, 2010 9:32 AM
    Monday, September 13, 2010 9:11 AM
    Answerer
  • HI Dimant

    you solve my problem

    but in my value column i get the  value 25.65555666 or 3.2589688 etc but i wont it to be 25.65 and 3.25 respectively
    i tried round function but not able to implement it in above case
    in my query i have

     WHERE CASE WHEN b.value LIKE '%[^2-9.]%' THEN 0 ELSE 1 END = 1 ...so where and how to use round function so as  to get value like 25.65 and 3.25 or 45.48(two places after decimal) or there is some other way to achieve this

    Monday, September 13, 2010 9:37 AM
  • insert

     

    into

     

    #t values ('25.65555666')

    ---SELECT

    select

     

    CAST(c AS DECIMAL(5,2))

    from

     

    #t

    WHERE

     

    CASE WHEN c LIKE '%[^0-9.]%' THEN 0

     

    ELSE 1 END = 1


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, September 13, 2010 10:10 AM
    Answerer
  • looks like you have CI (case insensitive) collation and may be there is some values that are like 2a , 2b etc ...can you cross check if all the values are 2,3 23 etc ...

    I did a repro :

    select

     

    cast(Value as decimal(18,0)) from FACT_AttributeMeasure where value <2

    select

     

    cast(isnull(value,0) as float) from FACT_AttributeMeasure where value =2

    it works fine .....

    but if I insert 2a as well then I get the errors as you are getting...

    Just see if that is the case ....

    Regards

     


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Monday, September 13, 2010 10:41 AM