Is there CLEAN function, or similar, similar to the Excel function>

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  July 22, 2019 at 8:22 pm

  #3665211

  The Tables I use often times have control characters in them, like CRs and LFs.  The data also has spaces.

  I know how to remove spaces, but how can I a function like CLEAN (in Excel) in my SSMS report??

  possible?

   

  thanks

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  July 22, 2019 at 9:59 pm

  #3665222

  PatExclude or PatReplace can do the trick.

  For example, this will remove any characters that are not alphanumeric while preserving spaces (note the space after the "Z"):

  SELECT f.* FROM dbo.PatExclude8K(<yourstring>, '[^0-9a-zA-Z ]') AS f;

  note that this is an Inline table valued function (because they are faster than scalar). So you'll need to understand how to use APPLY.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  July 22, 2019 at 10:44 pm

  #3665228

  Thanks!!