TSQL Convert 3.100000000000e+003 to human readable format (Double)

• TcW_1978

  SSCrazy

  Points: 2018

  More actions

  February 4, 2013 at 6:35 am

  #269759

  I have a stored procedure that returns a XML string, I have a field 'Quantities' which contains an odd value : 3.100000000000e+003.

  I know it's a numeric value and it is valid but is there a way to format it in TSQL so it's human readable?

  Thanks.

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  February 4, 2013 at 6:50 am

  #1583930

  Something like this?

  --== SAMPLE DATA ==--

  DECLARE @XML XML = '<YourXML><TheData value="3.100000000000e+003" /></YourXML>';

  --== VIEW SAMPLE DATA==--

  SELECT @XML;

  /* Looks like this: -

  <YourXML>

  <TheData value="3.100000000000e+003" />

  </YourXML>

  */

  --==Convert from scientific notation==--

  SELECT Data.value('(@value)[1]', 'REAL')

  FROM @XML.nodes('./YourXML/TheData') yourXMLData(Data);

  If not. . . supply sample data and expected result-set in your question. Otherwise, people just have to guess what you want.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• TcW_1978

  SSCrazy

  Points: 2018

  More actions

  February 4, 2013 at 7:08 am

  #1583936

  Thank you, yes, I just came around the problem with the same type of solution.

  Something like : str(cast('252536e+003' as real) )

  Thanks again.