Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Conversion of datatype varchar to float

Answered Conversion of datatype varchar to float

  • Thursday, January 17, 2013 12:41 PM
     
      Has Code
    Sign In to Vote
    0

    Hi All,

    please tell me where i am going wrong..

    table_MSISDN
MSISDN
'919133309659'
'919133756659'
'919133596959'
'919133681559'
'919133189159'
'919133369859'
'919133848059'
'919133330459'
'919133151459'
'919133183759'
'919133191159'
'919133648059'
'919133813859'
'919133311459'
'919133780359'
'919133915059'
'919133337459'
'919133773159'
'919133645259'
'919133252659'
'919133433859'
'919133559959'
'919133878659'
'919133758159'
'919133629959'
'919133555159'
'919133733459'
'919133832259'
'919133287659'
'919133940459'
'919133890559'
'919133266759'
'919133841659'
'919133912359'
'919297051959'

    select SUBSTRING(str(convert(float,msisdn),12,0),-10,10) from table_msisdn

    GSKR

     

All Replies

  • Thursday, January 17, 2013 12:41 PM
     
      Has Code
    Sign In to Vote
    0

    And getting an error...

    Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to float.

    GSKR

     
  • Thursday, January 17, 2013 12:47 PM
     
      Has Code
    Sign In to Vote
    0

    try this

    select SUBSTRING(str(convert(float,Replace(msisdn,'''','')),12,0),-10,10) from table_msisdn

    Please have look on the comment

    • Proposed As Answer by vr.babu Thursday, January 17, 2013 12:54 PM
    • Unproposed As Answer by GSKR Thursday, January 17, 2013 12:54 PM
    •  
     
  • Thursday, January 17, 2013 12:49 PM
     
     
    Sign In to Vote
    0
    are u sure with thw second argument u r passing for substring i.e. -10

    Please have look on the comment

     
  • Thursday, January 17, 2013 12:51 PM
     
     
    Sign In to Vote
    0

    Hi Santhosh,

    Thanks for your reply i have 4lacs records as varchar datatype and when i run this select SUBSTRING(str(convert(float,Replace(msisdn,'''','')),12,0),-10,10) from table_msisdn am able to get only 2K records only...

    sorry santhosh records are returning blank records...

    GSKR


    • Edited by GSKR Thursday, January 17, 2013 12:53 PM
    •  
     
  • Thursday, January 17, 2013 12:55 PM
     
     
    Sign In to Vote
    0

    Hey,

    selectSUBSTRING(str(convert(float,Replace(msisdn,'''','')),12,0),-10,10) from table_msisdn

    this statement doesn't have any filter condition so it shud fetch all those records from the table.

    Second is that are u using distinct clause by any chance also the second parameter for substring is passed as -10 i am not sure why u r passing that.

    Please have look on the comment

     
  • Thursday, January 17, 2013 12:55 PM
     
     
    Sign In to Vote
    0
    Could you please let us know your desired output? We would be able to help you in better.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Thursday, January 17, 2013 1:00 PM
     
     
    Sign In to Vote
    0

    Hey GSK,

    its returning blan records becos the second substring parameter u r passing is -10.Please chk that.

    Please have look on the comment

     
  • Thursday, January 17, 2013 1:01 PM
     
      Has Code
    Sign In to Vote
    0

    Santhosh,

    i have 4Lacs records in varchar datatype and i want 10 numbers starting from 913 and it shud return as float datatype.below is the example table

    table_MSISDN	
MSISDN,charchar(255)	after quering- MSISDN,FLOAT
'919133309659'	9133309659
'919133756659'	9133756659
'919133596959'	9133596959
'919133681559'	9133681559
'919133189159'	9133189159
'919133369859'	9133369859
'919133848059'	9133848059
'919133330459'	9133330459
'919133151459'	9133151459
'919133183759'	9133183759
'919133191159'	9133191159
'919133648059'	9133648059
'919133813859'	9133813859
'919133311459'	9133311459
'919133780359'	9133780359
'919133915059'	9133915059
'919133337459'	9133337459
'919133773159'	9133773159
'919133645259'	9133645259
'919133252659'	9133252659
'919133433859'	9133433859
'919133559959'	9133559959
'919133878659'	9133878659
'919133758159'	9133758159
'919133629959'	9133629959
'919133555159'	9133555159
'919133733459'	9133733459
'919133832259'	9133832259
'919133287659'	9133287659
'919133940459'	9133940459
'919133890559'	9133890559
'919133266759'	9133266759
'919133841659'	9133841659
'919133912359'	9133912359
'919297051959'	9297051959

    GSKR

     
  • Thursday, January 17, 2013 1:04 PM
     
      Has Code
    Sign In to Vote
    0

    Can you try the below:

    Drop table table_MSISDN
Create Table table_MSISDN(MSISDN Varchar(100))
Insert into table_MSISDN Select '''919133309659'''
Select MSISDN,Cast(Left(SUBSTRING(MSISDN,4,LEN(MSISDN)),10) as float) From table_MSISDN

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Thursday, January 17, 2013 1:07 PM
     
     
    Sign In to Vote
    0

    What is the purpose of the SUBSTRING?  According to the T_SQL Books Online reference (http://msdn.microsoft.com/en-us/library/ms187748.aspx), SUBSTRING will return an empty string when:

    "If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned".

    Please specify the purpose of SUBSTRING and the results you expect.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Thursday, January 17, 2013 5:40 PM
     
      Has Code
    Sign In to Vote
    0

    What about this?

    DECLARE @x VARCHAR(12) = '919133309659';
DECLARE @y VARCHAR(14) = '''919133309659''';

SELECT @x, CONVERT(FLOAT, RIGHT(@x, 10)), @y, CONVERT(FLOAT, RIGHT(REPLACE(@y, '''', ''), 10))

     
  • Friday, January 18, 2013 4:07 AM
     
     
    Sign In to Vote
    0

    Hi Rogge,

    Thanks for your reply. The query is working but am able to get only 2K records out of 4L records..

    GSKR

     
  • Friday, January 18, 2013 4:35 AM
     
     
    Sign In to Vote
    0

     but am able to get only 2K records out of 4L records..


    There is no WHERE clause in the query to filter the result and there is no TOP clause to limit the result set, so there is no SQL Server sided reason, why the reusltset is limited. Does maybe your client tool limit the rows it shows?

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 4:54 AM
     
      Has Code
    Sign In to Vote
    0

    Hi Olaf,

    here is the error..and in resultset am getting only 2K records..

    /*------------------------
Select MSISDN,Cast(Left(SUBSTRING(MSISDN,4,LEN(MSISDN)),10) as float) From table_MSISDN
------------------------*/
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to float.

    GSKR


    • Edited by GSKR Friday, January 18, 2013 4:56 AM
    •  
     
  • Friday, January 18, 2013 5:16 AM
     
     Answered Has Code
    Sign In to Vote
    1

    Are you sure that the format is always '919133309659' ?

    I assume there would be some data which have character/special character.

    Please try the below:

    --Drop table table_MSISDN
Create Table table_MSISDN(MSISDN Varchar(100))
Insert into table_MSISDN Select '''919133309659'''
Insert into table_MSISDN Select '''9191333096''9'''
--Below is error
Select MSISDN,Cast(Left(SUBSTRING(MSISDN,4,LEN(MSISDN)),10) as float) From table_MSISDN 
--No error
Select MSISDN,Cast(Left(SUBSTRING(MSISDN,4,LEN(MSISDN)),10) as float)  From table_MSISDN
Where Left(SUBSTRING(MSISDN,4,LEN(MSISDN)),10) NOT LIKE '%[^0-9]%'


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked As Answer by GSKR Friday, January 18, 2013 5:29 AM
    •  
     
  • Friday, January 18, 2013 5:29 AM
     
     
    Sign In to Vote
    0

    Hi Latheesh,

    solved man..... u r superb.....dude

    GSKR