locked
Strip non-numeric characters from string RRS feed

  • Question

  • If I have a string with the following values, I’d like to replace the non-numeric characters with a space.

    Input

    01234-987

    012345678

    01234 ext 65656

    Tel 0123456

    012345 898989

    Output

    01234 987

    012345678

    01234 65656

    0123456

    012345 898989

    Monday, July 27, 2015 10:55 AM

Answers

  • See also my blog 

    Remove Bad Characters from a string

    referenced in my article

    T-SQL: How to Find Rows with Bad Characters


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


    My blog


    My TechNet articles


    Monday, July 27, 2015 12:32 PM
  • Hello - I did some more customization, can you test this now:

    ALTER FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @intAlpha INT
    
    -- SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric)
    
    BEGIN
    
    WHILE @intAlpha > 0
    BEGIN
    	SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ' ' )
    	-- SET	@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    	SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric)
    	
    END
    
    END
    	RETURN ISNULL(@strAlphaNumeric,0)
    END
    
    DECLARE @strData VARCHAR(50)
    SET @strData = '012345-EXT9981'
    SELECT dbo.udf_GetNumeric(@strData)

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, July 27, 2015 12:33 PM

All replies

  • Use the below function

    CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
    RETURNS VARCHAR(1000)
    BEGIN
      DECLARE @pos INT
      SET @Pos = PATINDEX('%[^0-9]%',@Input)
      WHILE @Pos > 0
       BEGIN
        SET @Input = STUFF(@Input,@pos,1,'')
        SET @Pos = PATINDEX('%[^0-9]%',@Input)
       END
      RETURN @Input
    END
    GO


    Please Dont forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Milan Das


    • Edited by Milan Das Monday, July 27, 2015 11:07 AM
    Monday, July 27, 2015 11:06 AM
  • Milan,

    This function will not work since Jez wants to replace a non numeric by a space. The function you suggested will run in a loop without end.

    Jez, refer to this link (Gert-Jan Strik query) and replace the '' by ' '. Il will work.

    https://social.msdn.microsoft.com/Forums/en-US/57dd1e4c-ff08-4c86-80a7-3c1ca9df38e5/fastest-way-to-replace-non-numeric-characters-from-string?forum=transactsql

    Hope it helps!!


    Please click "Mark As Answer" if my post helped.

    Monday, July 27, 2015 11:11 AM
  • One way this could be achieved would be to: -

    Create a reference table for all the characters and add all the character data, you will only need a varchar(1) field for this.

    Next use a cursor to load each character in turn into a variable and then update your data with the REPLACE function.

    Reply back if you want a code example.


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, July 27, 2015 11:11 AM
  • Hello - You can use the following script:

    -- Creating Parser function
    CREATE FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @intAlpha INT
    
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
    
    WHILE @intAlpha > 0
    BEGIN
    	SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
    	SET	@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    END
    
    END
    	RETURN ISNULL(@strAlphaNumeric,0)
    END
    
    
    -- Using Parser function with Test Data
    DECLARE @strData VARCHAR(50)
    SET @strData = '012345-EXT9981'
    SELECT dbo.udf_GetNumeric('012345-EXT9981')
    
    SET @strData = '012345 9981'
    SELECT dbo.udf_GetNumeric('012345-EXT9981')

    Hope this helps

    Script courtesy: http://stackoverflow.com/questions/16667251/query-to-get-only-numbers-from-a-string


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, July 27, 2015 11:12 AM
  • Hi, thanks for the reply, unfortunately the function doesn’t quite work as I need to preserve any spaces in the input ie

    Input

    012345 898989

    Output

    012345 898989
    Monday, July 27, 2015 11:20 AM
  • Hi could you please send a code example.  Will it preserve spaces in the input?
    Monday, July 27, 2015 11:21 AM
  • Hi, thanks for the reply, unfortunately the function doesn’t quite work as I need to preserve any spaces in the input ie

    Input

    012345 898989

    Output

    012345 898989
    Monday, July 27, 2015 11:21 AM
  • Hi, thanks for the reply, unfortunately the function doesn’t quite work as I need to preserve any spaces in the input ie

    Input

    012345 898989

    Output

    012345 898989
    Monday, July 27, 2015 11:22 AM
  • This is why I put my suggestion in...

    If you use a reference table as your source data as to what you want to replace it will give you greater control on what you want to replace as you can add and delete what data you want to replace as you wish.


    Please click "Mark As Answer" if my post helped. Tony C.


    Monday, July 27, 2015 11:25 AM
  • Could you please provide an example as I'm a little confused how this will work. Thanks
    Monday, July 27, 2015 11:28 AM
    • Proposed as answer by Naomi N Monday, July 27, 2015 12:16 PM
    Monday, July 27, 2015 11:29 AM
  • Hi, yes I did try the solution in the link but it doesn’t work if there is a space in the input ie

    Input

    012345 898989

    Output

    012345 898989
    Monday, July 27, 2015 11:44 AM
  • Hello Jez - I have modified the script to suit your requirement. Please test this:

    ALTER FUNCTION dbo.udf_GetNumeric (@strAlphaNumeric VARCHAR(256)) RETURNS VARCHAR(256) AS BEGIN DECLARE @intAlpha INT -- SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric) SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric) BEGIN WHILE @intAlpha > 0 BEGIN SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) -- SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric ) SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric) END END RETURN ISNULL(@strAlphaNumeric,0) END

    -- Sample Test & Invocations DECLARE @strData VARCHAR(50) SET @strData = '012345-EXT9981' SELECT dbo.udf_GetNumeric(@strData) SET @strData = '012345 9981' SELECT dbo.udf_GetNumeric(@strData) SET @strData = '012XYZ34599EXT81' SELECT dbo.udf_GetNumeric(@strData)


    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue


    • Edited by Manu Kapoor Monday, July 27, 2015 11:45 AM Enhancements
    Monday, July 27, 2015 11:44 AM
  • Try this: -

    Create your table
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING OFF
    GO
    CREATE TABLE [dbo].[CharReferences](
     [CharValue] [varchar](1) NOT NULL
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    Load some character data in it; if your instance or database is case sensitive include entries for upper and lower case characters.

    Create a procedure: -

    CREATE Procedure [dbo].[usp_ReplaceNoneNumericalCharacters] AS
    BEGIN
    Declare
     @CharVal NVarChar(255),
     @QueryStmnt NVarChar(max)
    Declare Char_CRS Cursor for Select CharValue from CharReferences
    Open Char_CRS
    Fetch Next from Char_CRS INTO @CharVal
    While @@FETCH_STATUS=0
    BEGIN

     SET @QueryStmnt=(Select 'UPDATE YOURTABLE SET YOURCOLUMN=replace('+''''+@CharVal+''''+',''' + ' '+''''+')')
     EXECUTE sp_executesql @QueryStmnt
     --print @QueryStmnt
     Fetch Next from Char_CRS INTO @CharVal
    END
    CLOSE Char_CRS
    DEALLOCATE Char_CRS

    GO

    Obviously replace the YOURTABLE and YOUCOLUMN with your object names and test this on some Test Data


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, July 27, 2015 11:55 AM
  • Hi thanks for the reply, for the input of '012345-EXT9981', the output should be '012345 9981'<o:p></o:p>


    Monday, July 27, 2015 12:00 PM
  • So, simply add a space in that solution, e.g.

    declare @Phones table (id int identity primary key, phone varchar(20))
    insert into @Phones (phone)
    values
    ('01234-987'),
    ('012345678'),
    ('01234 ext 65656'),
    ('Tel 0123456'),
    ('012345 898989')
    
    
    --Output
    
    --01234 987
    
    --012345678
    
    --01234 65656
    
    --0123456
    
    --012345 898989
    
    select *, 
     CASE WHEN SUBSTRING(Phone,1,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,1,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,2,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,2,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,3,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,3,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,4,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,4,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,5,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,5,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,6,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,6,1) ELSE '' END
    
    +CASE WHEN SUBSTRING(Phone,7,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,7,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,8,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,8,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,9,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,9,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,10,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,10,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,11,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,11,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,12,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,12,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,13,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,13,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,14,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,14,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,15,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,15,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,16,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,16,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,17,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,17,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,18,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,18,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,19,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,19,1) ELSE '' END
    +CASE WHEN SUBSTRING(Phone,20,1) LIKE '[0-9 ]' THEN SUBSTRING(Phone,20,1) ELSE '' END
    
    from @Phones
    


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


    My blog


    My TechNet articles

    Monday, July 27, 2015 12:27 PM
  • See also my blog 

    Remove Bad Characters from a string

    referenced in my article

    T-SQL: How to Find Rows with Bad Characters


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


    My blog


    My TechNet articles


    Monday, July 27, 2015 12:32 PM
  • Hello - I did some more customization, can you test this now:

    ALTER FUNCTION dbo.udf_GetNumeric
    (@strAlphaNumeric VARCHAR(256))
    RETURNS VARCHAR(256)
    AS
    BEGIN
    DECLARE @intAlpha INT
    
    -- SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric)
    
    BEGIN
    
    WHILE @intAlpha > 0
    BEGIN
    	SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, ' ' )
    	-- SET	@intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
    	SET @intAlpha = PATINDEX('%[^0-9 '']%', @strAlphaNumeric)
    	
    END
    
    END
    	RETURN ISNULL(@strAlphaNumeric,0)
    END
    
    DECLARE @strData VARCHAR(50)
    SET @strData = '012345-EXT9981'
    SELECT dbo.udf_GetNumeric(@strData)

    Hope this helps !


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    Monday, July 27, 2015 12:33 PM
  • Hi thanks for the reply, for the input of '012345-EXT9981', the output should be '012345 9981'<o:p></o:p>


    Amend the stored procedure and add this line after the DEALLOCATE Statement to remove excessive white space

    UPDATE YOURTABLE SET YOURCOLUMN=replace('  ',' ')


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, July 27, 2015 12:44 PM