Post reply

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

  • 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

  • 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

  • July 22, 2019 at 10:44 pm

    #3665228

    Thanks!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply