locked
Filter Non-Decimal Characters RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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