Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Numerical conversion RE: Numerical conversion

  • January 3, 2018 at 1:49 am

    #1974169

    Sue_H - Friday, December 29, 2017 4:30 PM

    Luis Cazares - Friday, December 29, 2017 8:56 AM

    Jack Corbett - Friday, December 29, 2017 8:47 AM

    This is an even simpler option:

    WITH VTE AS
      (
       SELECT
        *
       FROM
        (
          VALUES (3),
            (25.75),
            (12),
            (125.8),
            (1000),
            (1857.5)
        ) V (I)
      )
    SELECT VTE.I, FORMAT(I*10, '0000') AS LeadingZeros FROM VTE;

    I usually avoid FORMAT due to its fame of being slow.

    I was just comparing the two for that very reason....format version is much slower on execution times as I thought it would be. Quite noticeable with a cold cache even with a small set of data. Keep thinking maybe there is some scenario where it is more useful, isn't so slow but haven't found one yet.

    Sue

    Agreed, I remember about 4 years ago when I got access to FORMAT on 2012, and I was really excited. Started using it, and noticed a significant performance decrease. Couldn't see at all as to why; then someone (here) posted about FORMAT and it's awful performance; i changed the FORMAT back to CONVERT and they query runs beautifully again.

    A real shame, as FORMAT is so much easier on the eyes; something that starts with REPLACE(LEFT(CONVERT(varchar(15), and ends with something like ,121),7),'-','') just isn't as easily understood by some.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk