How to remove string

• monilps

  SSCommitted

  Points: 1686

  More actions

  September 23, 2014 at 1:20 pm

  #290951

  Hi,

  TableA

  Col1

  ----

  13.4

  13.4 a

  13..4

  13,.4

  Result for Table A needed:

  Col1 ColCalculated

  --------------------------

  13.4 13.4

  13.4 a null

  13..4 null

  13,.4 null

  Please let me know how can I achieve it.

  Thanks in advance.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 23, 2014 at 1:44 pm

  #1747634

  In SQL Server 2012, you can use TRY_PARSE(), TRY_CONVERT() or TRY_CAST().

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  September 23, 2014 at 2:16 pm

  #1747648

  Simple and maybe not perfect approach is like Luis mentioned, using try_parse. The drawback is that some unwanted values may parse but then again, that might be fine.

  😎

  ;WITH TableA(Col1)

  AS (SELECT * FROM (VALUES

  ('13.4' )

  ,('13.4 a' )

  ,('13..4' )

  ,('13,.4' )) AS X(Col1))

  SELECT

  TA.Col1

  ,TRY_PARSE(TA.Col1 AS DECIMAL(12,2)) AS CalculatedColumn

  FROM TableA TA

  Results

  Col1 CalculatedColumn

  ------ -----------------

  13.4 13.40

  13.4 a NULL

  13..4 NULL

  13,.4 13.40

• monilps

  SSCommitted

  Points: 1686

  More actions

  September 23, 2014 at 2:25 pm

  #1747654

  Great 🙂

  Thanks