T-SQL trim (and other non-alphanumeric characters)

Question

We have some input data that sometimes appears with &nbsp characters on the end.

The data comes in from the source system as varchar() and our attempts to cast as decimal fail b/c of these characters.

Ltrim and Rtrim don't remove the characters, so we're forced to do something like:

UPDATE myTable
SET myColumn = replace(myColumn,char(160),'')
WHERE charindex(char(160),myColumn) > 0

This works for the &nbsp, but is there a good way to do this for any non-alphanumeric (or in this case numeric) characters?

score 17 · Answer 1 · edited Aug 16 '10 at 08:39

This will remove all non alphanumeric chracters

CREATE FUNCTION [dbo].[fnRemoveBadCharacter]
(
    @BadString nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN

            DECLARE @nPos INTEGER
            SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)

            WHILE @nPos > 0
            BEGIN
                        SELECT @BadString = STUFF(@BadString, @nPos, 1, '')
                        SELECT @nPos = PATINDEX('%[^a-zA-Z0-9_]%', @BadString)
            END

            RETURN @BadString
END

Use the function like:

UPDATE TableToUpdate
SET ColumnToUpdate = dbo.fnRemoveBadCharacter(ColumnToUpdate)
WHERE whatever

score 9 · Accepted Answer · answered Sep 09 '08 at 16:46

9

This page has a sample of how you can remove non-alphanumeric chars:

-- Put something like this into a user function:
DECLARE @cString    VARCHAR(32)
DECLARE @nPos    INTEGER
SELECT  @cString = '90$%45623 *6%}~:@'
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)

WHILE @nPos > 0
BEGIN
SELECT @cString = STUFF(@cString, @nPos, 1, '')
SELECT  @nPos = PATINDEX('%[^0-9]%', @cString)
END

SELECT @cString

answered Sep 09 '08 at 16:46

Espo

41,399
21
132
159

this removes all but 0-9 (numbers). .... so it's not really doing _alpha_ numberic :P .. – Pure.Krome Feb 19 '10 at 04:54

score 1 · Answer 3 · answered Sep 09 '08 at 17:03

1

How is the table being populated? While it is possible to scrub this in sql a better approach would be to change the column type to int and scrub the data before it's loaded into the database (SSIS). Is this an option?

answered Sep 09 '08 at 17:03

jason saldo

9,804
5
34
41

score 0 · Answer 4 · answered Mar 10 '15 at 17:47

For large datasets I have had better luck with this function that checks the ASCII value. I have added options to keep only alpha, numeric or alphanumeric based on the parameters.

--CleanType 1 - Remove all non alpanumeric
--          2 - Remove only alpha
--          3 - Remove only numeric
CREATE FUNCTION [dbo].[fnCleanString] (
        @InputString    varchar(8000)
    ,   @CleanType      int 
    ,   @LeaveSpaces    bit 
)   RETURNS varchar(8000)
AS 
BEGIN

    -- // Declare variables
    -- ===========================================================
    DECLARE @Length     int
        ,   @CurLength  int = 1
        ,   @ReturnString varchar(8000)=''

    SELECT @Length = len(@InputString)

    -- // Begin looping through each char checking ASCII value
    -- ===========================================================
    WHILE (@CurLength <= (@Length+1))
    BEGIN
        IF  (ASCII(SUBSTRING(@InputString,@CurLength,1)) between 48 and 57      AND @CleanType in (1,3) )
        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    between 65 and 90   AND @CleanType in (1,2) )
        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    between 97 and 122  AND @CleanType in (1,2) )
        or  (ASCII(SUBSTRING(@InputString,@CurLength,1))    = 32    AND @LeaveSpaces = 1 )
        BEGIN
            SET @ReturnString = @ReturnString + SUBSTRING(@InputString,@CurLength,1)
        END
        SET @CurLength = @CurLength + 1
    END

    RETURN  @ReturnString
END

score 0 · Answer 5 · edited Mar 09 '16 at 12:45

If the mobile could start with a Plus(+) I will use the function like this

CREATE FUNCTION [dbo].[Mobile_NoAlpha](@Mobile VARCHAR(1000)) 
RETURNS VARCHAR(1000) 
AS 
BEGIN
    DECLARE @StartsWithPlus BIT = 0

    --check if the mobile starts with a plus(+)
    IF LEFT(@Mobile, 1) = '+'
    BEGIN
        SET @StartsWithPlus = 1

        --Take out the plus before using the regex to eliminate invalid characters
        SET @Mobile = RIGHT(@Mobile, LEN(@Mobile)-1) 
    END

    WHILE PatIndex('%[^0-9]%', @Mobile) > 0 
        SET @Mobile = Stuff(@Mobile, PatIndex('%[^0-9]%', @Mobile), 1, '')  

    IF @StartsWithPlus = 1
        SET @Mobile = '+' + @Mobile
    RETURN @Mobile 
END

T-SQL trim (and other non-alphanumeric characters)

5 Answers5

Linked