Post reply

Scalar Function usnig regex to trim unprintable chars from a string

  • November 1, 2018 at 3:23 am

    #409238

    Hi, I have the following function that uses regex to trim all characters from a string that are not in the specified pattern list.
    Unfortunately it is leaving some non printable characters in the string and I am out of ideas.

    Can anyone advise please?

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER FUNCTION [dbo].[fn_StripCharacters]

    (

    @String VARCHAR(1000),

    @MatchExpression VARCHAR(255)

    )

    RETURNS VARCHAR(1000)

    AS

    BEGIN

    SET @MatchExpression = '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0

    SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

    END

    The pattern I am using is:

    '[^a-zA-Z0-9 ]'

    Sample string is attached in the text file.

     There seems to be a carriage return or new line char in there some where as it is breaking the line when inporting the text file using SSIS flatfile importer.

    I am using 2008 R2 and BIDS.

    Cheers

    Dave


  • November 1, 2018 at 9:24 am

    #2012064

    UPDATE:-
    I was running the function in a view.
    I forgot to repoint my SSIS OLEDB source from the underlying table to the view.....rookie mistake towards the end of an 18 Hour Workday.

    The function it turns out is really useful. Just add whatever Chars or ranges you want to keep into the pattern.

    Dave

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

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