Identifying all the values in a column that can be cast as numeric(3,1) in SQL Server 2008R2

• GPO

  SSCarpal Tunnel

  Points: 4574

  More actions

  February 6, 2017 at 5:18 pm

  #321516

  Hi All 
  I don't have control over the SQL Server version I'm using to resolve this problem (so functions like TRY_CONVERT etc are out). I have a VARCHAR(7) column of values that need to be converted (where possible) to numeric(3,1). I can't change this data type. Where not possible to convert to numeric(3,1), return a NULL. Functions like ISNUMERIC() might not be useful because ISNUMERIC('100.00') will return a 1 but '100.00' can't be cast as numeric(3,1). Instead it throws an overflow error.

  This is the sort of thing I'm trying at the moment. It's hideous. It's a maintenance nightmare, It's... it's... I... just... can't...
  SELECT mj.my_column
  FROM #my_junk mj
  WHERE
--convertible five decimal places:
     mj.my_column like '[0-9].[0-9][0-9][0-9][0-9][0-9]'  -- eg -1.23456, len = 7 chars
--convertible four decimal places:
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9][0-9]'  -- eg -1.2345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9][0-9]'  -- eg 1.2345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9][0-9][0-9]' -- eg 12.3456, len = 7
--convertible three decimal places:
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9]'    -- eg -1.234
    OR mj.my_column like '[0-9].[0-9][0-9][0-9]'    -- eg 1.234
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9]'    -- eg -12.345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9]'    -- eg 12.345
--convertible two decimal places:
    OR mj.my_column like '-[0-9][0-9].[0-9][0-9]'    -- eg -12.34
    OR mj.my_column like '[0-9][0-9].[0-9][0-9]'    -- eg 12.34
    OR mj.my_column like '-[0-9].[0-9][0-9]'     -- eg -1.23
    OR mj.my_column like '[0-9].[0-9][0-9]'      -- eg 1.23
--convertible one decimal place:
    OR mj.my_column like '-[0-9].[0-9]'       -- eg -1.2
    OR mj.my_column like '[0-9].[0-9]'       -- eg 1.2
    OR mj.my_column like '-[0-9][0-9].[0-9]'     -- eg -12.3
    OR mj.my_column like '[0-9][0-9].[0-9]'      -- eg 12.3
--convertible integers:
    OR mj.my_column like '-[0-9]'         -- eg -1
    OR mj.my_column like '[0-9]'         -- eg 1
    OR mj.my_column like '-[0-9][0-9]'       -- eg -12
    OR mj.my_column like '[0-9][0-9]'        -- eg 12

  As a supplementary exercise I've discovered some unexpected weirdness on ORDER BY for strings starting with a "-" character. Take a look at the following code and write down the expected results first (no cheating) THEN run the code.

  SELECT cast('1.00' as varchar(7)) as text_number
  UNION ALL
 SELECT cast('-1.00' as varchar(7))
  UNION ALL
 SELECT cast('1.01' as varchar(7))
  UNION ALL
 SELECT cast('-1.01' as varchar(7))
  UNION ALL
 SELECT cast('-1' as varchar(7))
  UNION ALL
 SELECT cast('2' as varchar(7))
  UNION ALL
 SELECT cast('-2' as varchar(7))
  UNION ALL
 SELECT cast('1' as varchar(7))
  UNION ALL
 SELECT cast('-' as varchar(7))
  UNION ALL
 SELECT cast('a' as varchar(7))
  UNION ALL
 SELECT cast('-a' as varchar(7))
ORDER BY text_number

  Huh?!?!?!

  ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

• Eddie Wuerch

  SSChampion

  Points: 12532

  More actions

  February 6, 2017 at 9:56 pm

  #1927009

  So, you want numeric values greater than (-100) and less than 100:
  CREATE TABLE #testr (id int not null primary key, OrigVarchar varchar(7) NOT NULL);
GO

  INSERT #testr(id, OrigVarchar)
VALUES (1, '00100.1'), (2, 'abcd'), (3, '-10.99'), (4, '10.2');
GO
SELECT * FROM #testr;
GO

  SELECT id, OrigVarchar, CONVERT(decimal(7, 1), OrigVarchar) AS CastToNumeric
  FROM #testr t
WHERE ISNUMERIC(OrigVarchar) = 1
   AND ABS(CONVERT(decimal(7, 1), OrigVarchar) ) < 100;
GO


  Eddie Wuerch
  MCM: SQL

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 7, 2017 at 2:07 am

  #1927023

  GPO - Monday, February 6, 2017 5:18 PM

  Hi All 
  I don't have control over the SQL Server version I'm using to resolve this problem (so functions like TRY_CONVERT etc are out). I have a VARCHAR(7) column of values that need to be converted (where possible) to numeric(3,1). I can't change this data type. Where not possible to convert to numeric(3,1), return a NULL. Functions like ISNUMERIC() might not be useful because ISNUMERIC('100.00') will return a 1 but '100.00' can't be cast as numeric(3,1). Instead it throws an overflow error.

  This is the sort of thing I'm trying at the moment. It's hideous. It's a maintenance nightmare, It's... it's... I... just... can't...
  SELECT mj.my_column
  FROM #my_junk mj
  WHERE
--convertible five decimal places:
     mj.my_column like '[0-9].[0-9][0-9][0-9][0-9][0-9]'  -- eg -1.23456, len = 7 chars
--convertible four decimal places:
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9][0-9]'  -- eg -1.2345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9][0-9]'  -- eg 1.2345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9][0-9][0-9]' -- eg 12.3456, len = 7
--convertible three decimal places:
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9]'    -- eg -1.234
    OR mj.my_column like '[0-9].[0-9][0-9][0-9]'    -- eg 1.234
    OR mj.my_column like '-[0-9].[0-9][0-9][0-9]'    -- eg -12.345
    OR mj.my_column like '[0-9].[0-9][0-9][0-9]'    -- eg 12.345
--convertible two decimal places:
    OR mj.my_column like '-[0-9][0-9].[0-9][0-9]'    -- eg -12.34
    OR mj.my_column like '[0-9][0-9].[0-9][0-9]'    -- eg 12.34
    OR mj.my_column like '-[0-9].[0-9][0-9]'     -- eg -1.23
    OR mj.my_column like '[0-9].[0-9][0-9]'      -- eg 1.23
--convertible one decimal place:
    OR mj.my_column like '-[0-9].[0-9]'       -- eg -1.2
    OR mj.my_column like '[0-9].[0-9]'       -- eg 1.2
    OR mj.my_column like '-[0-9][0-9].[0-9]'     -- eg -12.3
    OR mj.my_column like '[0-9][0-9].[0-9]'      -- eg 12.3
--convertible integers:
    OR mj.my_column like '-[0-9]'         -- eg -1
    OR mj.my_column like '[0-9]'         -- eg 1
    OR mj.my_column like '-[0-9][0-9]'       -- eg -12
    OR mj.my_column like '[0-9][0-9]'        -- eg 12

  As a supplementary exercise I've discovered some unexpected weirdness on ORDER BY for strings starting with a "-" character. Take a look at the following code and write down the expected results first (no cheating) THEN run the code.

  SELECT cast('1.00' as varchar(7)) as text_number
  UNION ALL
 SELECT cast('-1.00' as varchar(7))
  UNION ALL
 SELECT cast('1.01' as varchar(7))
  UNION ALL
 SELECT cast('-1.01' as varchar(7))
  UNION ALL
 SELECT cast('-1' as varchar(7))
  UNION ALL
 SELECT cast('2' as varchar(7))
  UNION ALL
 SELECT cast('-2' as varchar(7))
  UNION ALL
 SELECT cast('1' as varchar(7))
  UNION ALL
 SELECT cast('-' as varchar(7))
  UNION ALL
 SELECT cast('a' as varchar(7))
  UNION ALL
 SELECT cast('-a' as varchar(7))
ORDER BY text_number

  Huh?!?!?!

  There may be some mileage in checking the dollars and pennies separately:
  

  ;WITH SampleData AS (

  SELECT * FROM (VALUES

  ('-1.23456'),('-1.2345'),('1.2345'),('12.3456'),('-1.234'),('1.234'),('-12.345'),('12.345'),('-12.34'),('12.34'),('-1.23'),

  ('1.23'),('-1.2'),('1.2'),('-12.3'),('12.3'),('-1'),('1'),('-12'),('12')) d (MyValue)

  )

  SELECT

  MyValue,

  y.d, y.n,

  NewNumber = CASE WHEN y.d BETWEEN -99 AND 99 THEN y.d ELSE NULL END + '.' + CASE WHEN y.n BETWEEN 0 AND 9 THEN y.n ELSE NULL END

  FROM SampleData

  CROSS APPLY (SELECT d = PARSENAME(MyValue,2), n = PARSENAME(MyValue,1)) x

  CROSS APPLY (SELECT d = ISNULL(x.d, x.n), n = CASE WHEN d IS NULL THEN '0' ELSE x.n END) y

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ScottPletcher

  SSC Guru

  Points: 100942

  More actions

  February 7, 2017 at 11:21 am

  #1927171

  Perhaps this?:

  ;

  WITH my_junk AS (
 SELECT CAST(my_column as varchar(7)) as my_column FROM (VALUES
 ('-1.23456'),('-1.2345'),('1.2345000'),('12.3456'),('-1.234'),('1.234'),('-12.345'),('12.345'),('-12.34'),('12.34'),('-1.23'),
 ('ab'),('x'),(''),('0'),
 ('1.23'),('-1.2'),('1.2'),('-12.3'),('12.3'),('-1'),('1'),('-12'),('12')
 ) d (my_column)
)
SELECT my_column, final_value
FROM my_junk
CROSS APPLY (
  SELECT PARSENAME(my_column, 3) AS parse3, PARSENAME(my_column, 2) AS parse2, PARSENAME(my_column, 1) AS parse1
) AS ca1
CROSS APPLY (
  SELECT CASE WHEN parse2 > '' THEN parse2 ELSE parse1 END AS whole_numbers,
   CASE WHEN parse2 > '' THEN parse1 ELSE '' END AS decimals
) AS ca2
CROSS APPLY (
  SELECT CASE WHEN LEFT(whole_numbers, 1) IN ('-', '+') THEN LEFT(whole_numbers, 1) ELSE '' END AS sign
) AS ca3
CROSS APPLY (
  SELECT CASE WHEN whole_numbers LIKE sign + REPLICATE('[0-9]', LEN(whole_numbers) - LEN(sign)) AND
       (decimals = '' OR decimals LIKE '[0-9]' + REPLICATE('[0]', LEN(decimals) - 1))
      THEN 1
      ELSE 0 END AS values_are_numeric
) AS ca4
CROSS APPLY (
  SELECT CASE WHEN parse3 IS NULL AND values_are_numeric = 1 AND whole_numbers BETWEEN -99 AND 99 AND decimals BETWEEN 0 AND 9
   THEN CAST(my_column AS decimal(3, 1))
   ELSE NULL END AS final_value
) AS ca5

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• GPO

  SSCarpal Tunnel

  Points: 4574

  More actions

  February 8, 2017 at 5:07 pm

  #1927420

  Thanks all for your excellent suggestions, I particularly like Eddie Wuerch's which just needs a final cast to numeric(3,1). I'll test them and get back if I have any problems/observations.

  ...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell