none
Convert list values from 1440.00, 1450.00, 1450A to 1440 1450, 1450A RRS feed

  • Question

  • My sql statement returns values and as is converting them to decimal rather than leaving them as text.

    How can I force display to leave them as text?

    Thanks in advance....



    Monday, May 27, 2019 12:38 PM

All replies

  • Hi,

    You have to cast to varchar :

    CAST(Values AS VARCHAR(50))


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Monday, May 27, 2019 12:43 PM
  • SELECT CAST(CAST('1440.00' AS REAL)AS VARCHAR(10))

    But this is a text already, 

    1450A


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, May 27, 2019 12:44 PM
    Answerer
  • that is part of the challenge; all the values should be text. 

    TSQL is interpreting 1440 as a number and converting to 1440.00 ; and it is (correctly ) interpreting 1450A as text

    Monday, May 27, 2019 4:22 PM
  • No one can effectively provide help by guessing - and that is what you force others to do by posting a description of your query but not posting the query (or the related DDL). In addition, something "unique" is occurring if, for the same column, you think the query is intrepreting a value as numeric or string. If you are using Excel, then the issue is more likely to be with Excel and not with sql server. Regardless, help others help you - post the query, some sample data, and how you know that something is interpreted as numeric vs. text. 
    Monday, May 27, 2019 4:28 PM
  • Hi BadgerBoy14487,

    You could try below query to see whether it works or not

    select case when CHARINDEX('.',num)=0 then num else left(num,CHARINDEX('.',num)-1) end as a from (
    select '1440.00'  as num
    union all
    select '1450.00'  as num
    union all
    select '1440A'  as num)t
    
    
    /*
    a
    -------
    1440
    1450
    1440A
    
    (3 rows affected)
    */
    
    

    Best Regards,
    Zoe Zhi


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 28, 2019 3:15 AM