locked
Filter Non-Decimal Characters RRS feed

  • Question

  • I'm trying to create a function to remove all non-decimal characters from a string. I've found a number of examples of how to remove all special characters or how to remove all non alphanumerics or even how to remove all non-numeric characters. unfortunately, most of these also remove decimal points which I do not want to remove. I want to keep all numberic (0-9) and the decimal point.
    Thursday, December 1, 2011 10:12 PM

Answers

  • Hi Tech !

    I have written this awhile ago. Hopefully this will help you;

    http://gallery.technet.microsoft.com/DataCleanUp-Function-5f2d1c30

    USE [AdventureWorks] 
    GO 
     
    IF OBJECT_ID (N'dbo.DataCleanUp', N'FN') IS NOT NULL 
        DROP FUNCTION dbo.DataCleanUp; 
    GO 
    CREATE FUNCTION [dbo].[DataCleanUp](@Data VARCHAR(MAX),@InValidData VARCHAR(100)) 
    RETURNS VARCHAR(MAX) 
    AS 
    BEGIN 
     
    DECLARE @CleanUpData VARCHAR(MAX) 
    SET @CleanUpData = ' ' + @Data + ' ' 
     
    WHILE PATINDEX(@InValidData,@CleanUpData)>0  
    BEGIN 
    SET @CleanUpData = STUFF(@CleanUpData,PATINDEX(@InValidData,@CleanUpData),1,'') 
    END 
     
    RETURN LTRIM(RTRIM(@CleanUpData)) 
     
    END 
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

     

     

    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:46 PM
    Thursday, December 1, 2011 10:20 PM
    Answerer

All replies

  • See this

    Extracting numbers with SQL Server


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Thursday, December 1, 2011 10:19 PM
  • Hi Tech !

    I have written this awhile ago. Hopefully this will help you;

    http://gallery.technet.microsoft.com/DataCleanUp-Function-5f2d1c30

    USE [AdventureWorks] 
    GO 
     
    IF OBJECT_ID (N'dbo.DataCleanUp', N'FN') IS NOT NULL 
        DROP FUNCTION dbo.DataCleanUp; 
    GO 
    CREATE FUNCTION [dbo].[DataCleanUp](@Data VARCHAR(MAX),@InValidData VARCHAR(100)) 
    RETURNS VARCHAR(MAX) 
    AS 
    BEGIN 
     
    DECLARE @CleanUpData VARCHAR(MAX) 
    SET @CleanUpData = ' ' + @Data + ' ' 
     
    WHILE PATINDEX(@InValidData,@CleanUpData)>0  
    BEGIN 
    SET @CleanUpData = STUFF(@CleanUpData,PATINDEX(@InValidData,@CleanUpData),1,'') 
    END 
     
    RETURN LTRIM(RTRIM(@CleanUpData)) 
     
    END 
    


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks,
    Hasham

     

     

    • Marked as answer by Kalman Toth Tuesday, December 6, 2011 3:46 PM
    Thursday, December 1, 2011 10:20 PM
    Answerer