Post reply

Help on fixing unprintable char at end of varchar column

  • November 8, 2007 at 4:26 pm

    #176757

    we have funny and unprintable characters at the end to a company name. does anyone know how to use subtype to define as invalid any decimal ascii value of greater than 125 and using some function to truncate them? i am having trouble just selecting the bad company names.

  • November 8, 2007 at 6:34 pm

    #749688

    You can use a CLR function to scrub that out using regular expressions. You would have to enable CLR, publish this to the server, etc...

    Imports System

    Imports System.Data

    Imports System.Data.SqlClient

    Imports System.Data.SqlTypes

    Imports Microsoft.SqlServer.Server

    Imports System.Text.RegularExpressions

    Partial Public Class UserDefinedFunctions

    <SqlFunction()> _

    Public Shared Function RegexReplace(ByVal input As SqlChars, ByVal pattern As SqlString, ByVal rep As SqlString) As SqlString

    ' Add your code here

    Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value)

    Return New SqlString(rex.Replace(New String(input.Value), rep.Value))

    End Function

    End Class

    At that point you could do something like

    --all letters and alpha + space+ questionmark+parens

    select dbo.regexreplace(companyname,'[^a-zA-Z0-9 \?\(\)]','') from yourtable.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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