How would you parse 000.024.000 into 24H?

• David Burrows

  SSC Guru

  Points: 65138

  More actions

  March 29, 2018 at 1:49 am

  #1984913

  Obscure variation
  
REPLACE(LTRIM(REPLACE(REPLACE(REPLACE([value],'000.',''),'.000',''),'0',' ')),' ','0')+'H'


  Far away is close at hand in the images of elsewhere.
  Anon.

• Jason A. Long

  SSC-Insane

  Points: 23781

  More actions

  March 29, 2018 at 10:31 am

  #1984994

  Jeff Moden - Wednesday, March 28, 2018 4:23 PM

  Jason A. Long - Wednesday, March 28, 2018 10:58 AM

  Jeff Moden - Wednesday, March 28, 2018 7:44 AM

  NineIron - Wednesday, March 28, 2018 7:41 AM

  Nice. I'll use replace(PARSENAME(TextTime,2)+'H','0','') to remove the leading zero's but, this is perfect.

  No... do no use REPLACE here because it will change things like 020 to just 2.  Convert to an INT and then back to character based to add the "H".

  Or use the CONCAT function...
  DECLARE @TextTime VARCHAR(11) = '000.002.000';

  SELECT CONCAT(CONVERT(INT, PARSENAME(@TextTime, 2)), 'H');

  Damn.  I hate what MS does behind the scenes on these supposed improvements.  Here's what PARSNAME and CONCAT renders out to.  I'd forgotten that PARSNAME produces an NVARCHAR result.

  [Expr1093] = Scalar Operator(concat(CONVERT_IMPLICIT(varchar(12),CONVERT(int,parsename(CONVERT_IMPLICIT(nvarchar(11),[@TextTime],0),(2)),0),0),'H'))

  I agree that it looks extremely convoluted when you look at what's going on under the covers... I suppose the question is, does it matter if it's still able to efficiently process the request?
  Looking at the execution plan, I'm not seeing any sort of carnality warnings and it appears to be as good or better, in terms of performance, as other solutions.

  IF OBJECT_ID('tempdb..#test_data', 'U') IS NOT NULL
BEGIN DROP TABLE #test_data; END;
GO

  WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (n) AS (
        SELECT TOP (10000)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
SELECT
    test_value = CONCAT('000.', RIGHT(CONCAT('00', ABS(CHECKSUM(NEWID()) % 99 + 1)), 3), '.000')
    INTO #test_data
FROM
    cte_Tally t;
GO

  --====================================

  DECLARE @_duration VARCHAR(50) = 'control duration: ', @_begtime DATETIME2(7) = SYSDATETIME();
SELECT
    td.test_value
FROM
    #test_data td;
SELECT @_duration = CONCAT(@_duration, DATEDIFF(MICROSECOND, @_begtime, SYSDATETIME()), ' ms');
RAISERROR(@_duration, 0, 1);
GO
-----------------------------------------------
DECLARE @_duration VARCHAR(60) = 'concat w/ parsename duration: ', @_begtime DATETIME2(7) = SYSDATETIME();
SELECT
    td.test_value,
    time_value = CONCAT(CONVERT(INT, PARSENAME(td.test_value, 2)), 'H')
FROM
    #test_data td;
SELECT @_duration = CONCAT(@_duration, DATEDIFF(MICROSECOND, @_begtime, SYSDATETIME()), ' ms');
RAISERROR(@_duration, 0, 1);
GO
-----------------------------------------------
DECLARE @_duration VARCHAR(50) = 'concat w/ substring duration: ', @_begtime DATETIME2(7) = SYSDATETIME();
SELECT
    td.test_value,
    CONCAT(CONVERT(INT, pv.parsed_val), 'H')
FROM
    #test_data td
    CROSS APPLY ( VALUES (SUBSTRING(td.test_value, 5, 3)) ) pv (parsed_val);
SELECT @_duration = CONCAT(@_duration, DATEDIFF(MICROSECOND, @_begtime, SYSDATETIME()), ' ms');
RAISERROR(@_duration, 0, 1);
GO
-----------------------------------------------
DECLARE @_duration VARCHAR(50) = 'substring duration: ', @_begtime DATETIME2(7) = SYSDATETIME();
SELECT
    td.test_value,
    CONVERT(VARCHAR(10), CONVERT(INT, pv.parsed_val)) +' H'
FROM
    #test_data td
    CROSS APPLY ( VALUES (SUBSTRING(td.test_value, 5, 3)) ) pv (parsed_val);
SELECT @_duration = CONCAT(@_duration, DATEDIFF(MICROSECOND, @_begtime, SYSDATETIME()), ' ms');
RAISERROR(@_duration, 0, 1);
GO
-----------------------------------------------

  Results:

  control duration: 9015 ms
concat w/ parsename duration: 12996 ms
concat w/ substring duration: 13004 ms
substring duration: 14014 mss

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  March 29, 2018 at 12:40 pm

  #1985029

  Yep... agreed.  Performance is second only to accuracy and they're both extremely important.

  I was mostly referring to the bloody PARSENAME and the NVARCHAR conversion.  I do know that PARSENAME was invented to parse qualified object names, which are NVARCHAR(128), but good lord.  Imagine if someone used just PARSENAME as variable criteria against a varchar column.  SCAN!  At least CONCAT used the same datatype as the original literal.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)