Strip Out Non-Numeric Characters in Select

Select query to remove non-numeric characters

See this blog post on extracting numbers from strings in SQL Server. Below is a sample using a string in your example:

DECLARE @textval NVARCHAR(30)
SET @textval = 'AB ABCDE # 123'

SELECT LEFT(SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000),
           PATINDEX('%[^0-9.-]%', SUBSTRING(@textval, PATINDEX('%[0-9.-]%', @textval), 8000) + 'X') -1)

Remove all non numeric characters in sql SELECT

Some time ago I solved that problem using the below function

create function dbo.[fnrReplacetor](@strtext varchar(2000))
returns varchar(2000)
as
begin
    declare @i int = 32, @rplc varchar(1) = '';
    while @i < 256
    begin
        if (@i < 48 or @i > 57) and CHARINDEX(char(@i),@strtext) > 0
        begin
            --° #176 ~ 0   --¹ #185 ~ 1   --² #178 ~ 2   --³ #179 ~ 3
            set @rplc = case @i
            when 176 then '0'
            when 185 then '1'
            when 178 then '2'
            when 179 then '3'
            else '' end;

            set @strtext = REPLACE(@strtext,CHAR(@i),@rplc);
        end

        set @i = @i + 1;
    end
    return @strtext;
end

GO

select dbo.[fnrReplacetor]('12345/97')

Note it ill also consider characters °,¹,²,³ numeric and replace then with 0,1,2,3.

I put it in a function to readly reuse it in my scenario I needed to fix many columns in many tables at once.

update t
set t.myColumn = dbo.[fnrReplacetor](tempdb.myColumn)
 from test t
 where tempdb.myColumn is not null

or just

select dbo.[fnrReplacetor](tempdb.myColumn) as [Only Digits]
 from test t
where tempdb.myColumn is not null

Obs: this is not the fatest way but a thorough one.

Edit

A non UDF solution must be use REPLACE but since regex is not that great in SQL you can end doing something nasty like the below example:

declare @test as table (myColumn varchar(50))

insert into @test values ('123/45'),('123-4.5')

Select replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(myColumn,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z',''),'.',''),'-',''),'/','')
from @test

Strip out non-numeric characters in SELECT

You mentioned using a regular expression for this. It is true that Access' db engine doesn't support regular expressions directly. However, it seems you are willing to use a VBA user-defined function in your query ... and a UDF can use a regular expression approach. That approach should be simple, easy, and faster performing than iterating through each character of the input string and storing only those characters you want to keep in a new output string.

Public Function OnlyDigits(ByVal pInput As String) As String
    Static objRegExp As Object

    If objRegExp Is Nothing Then
        Set objRegExp = CreateObject("VBScript.RegExp")
        With objRegExp
            .Global = True
            .Pattern = "[^\d]"
        End With
    End If
    OnlyDigits = objRegExp.Replace(pInput, vbNullString)
End Function

Here is an example of that function in the Immediate window with "x" characters as proxies for your invisible characters. (Any characters not included in the "digits" character class will be discarded.)

? OnlyDigits("x1x23x")
123

If that is the output you want, just use the function in your query.

SELECT OnlyDigits(SomeCol) FROM SomeTable;

How to remove non-numeric characters (except full stop . ) from a string in amazon redshift

Please try this:

The below regex_replace expression will replace all character which are not ("^") in the (range of 0-9) & "."

SELECT regexp_replace('ABC$$$%%11633123.60','([^0-9.])','') FROM DUAL;

It returns the expected output "11633123.60"

Fastest way to remove non-numeric characters from a VARCHAR in SQL Server

I may misunderstand, but you've got two sets of data to remove the strings from one for current data in the database and then a new set whenever you import.

For updating the existing records, I would just use SQL, that only has to happen once.

However, SQL isn't optimized for this sort of operation, since you said you are writing an import utility, I would do those updates in the context of the import utility itself, not in SQL. This would be much better performance wise. What are you writing the utility in?

Also, I may be completely misunderstanding the process, so I apologize if off-base.

Edit:
For the initial update, if you are using SQL Server 2005, you could try a CLR function. Here's a quick one using regex. Not sure how the performance would compare, I've never used this myself except for a quick test right now.

using System;  
using System.Data;  
using System.Text.RegularExpressions;  
using System.Data.SqlClient;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  

public partial class UserDefinedFunctions  
{  
    [Microsoft.SqlServer.Server.SqlFunction]  
    public static SqlString StripNonNumeric(SqlString input)  
    {  
        Regex regEx = new Regex(@"\D");  
        return regEx.Replace(input.Value, "");  
    }  
};  

After this is deployed, to update you could just use:

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)

Remove all non-numeric characters from a field

There isn't any "builtin" function that will do this operation in MySQL.

One option is to create your own stored function (if you have sufficient privileges on the database).

 DELIMITER $$

 DROP FUNCTION IF EXISTS `uf_only_digits`$$

 CREATE FUNCTION `uf_only_digits`(as_val VARCHAR(65535))
 RETURNS VARCHAR(65535)
 DETERMINISTIC
 BEGIN
   DECLARE retval VARCHAR(65535);
   DECLARE i INT;
   DECLARE strlen INT;
   -- shortcut exit for special cases
   IF as_val IS NULL OR as_val = '' THEN
     RETURN as_val;
   END IF;
   -- initialize for loop
   SET retval = '';
   SET i = 1;
   SET strlen = CHAR_LENGTH(as_val);
 do_loop:
   LOOP
     IF i > strlen THEN
       LEAVE do_loop;
     END IF;
     IF SUBSTR(as_val,i,1) IN ('0','1','2','3','4','5','6','7','8','9') THEN
       SET retval = CONCAT(retval,SUBSTR(as_val,i,1));
     END IF;
     SET i = i + 1;
   END LOOP do_loop;
   RETURN retval;
 END$$

 DELIMITER ;

And be sure to test this before you use it an UPDATE statement.

 SELECT t.foo
      , uf_only_digits(t.foo)
   FROM ( SELECT '' AS foo
          UNION ALL SELECT ' x'
          UNION ALL SELECT 'a1b2'
          UNION ALL SELECT '1-888-555-1212 ext 213'
          UNION ALL SELECT '1-800-FLOWERS'
        ) t

Returns:

 foo                     uf_only_digits(t.foo)  newlen  
 ----------------------  ---------------------  --------
                                                       0
  x                                                    0
 a1b2                    12                            2
 1-888-555-1212 ext 213  18885551212213               14
 1-800-FLOWERS           1800                          4

(The last two rows might give us pause to reconsider what we really want to achieve. If it were me, I would creating a new column, and saving the existing value in it, before I did an UPDATE.)

 -- new column same size as `phone` column
 ALTER TABLE mytable ADD COLUMN orig_phone VARCHAR(40) NULL 
   COMMENT 'original phone value, before update to all digits';

 UPDATE mytable t
    SET t.orig_phone = t.phone ;

 UPDATE mytable t
    SET t.phone = uf_only_digits(t.phone) ;

Strip non-numeric characters from a string

RichardTheKiwi's script in a function for use in selects without cross apply,
also added dot because in my case I use it for double and money values within a varchar field

CREATE FUNCTION dbo.ReplaceNonNumericChars (@string VARCHAR(5000))
RETURNS VARCHAR(1000)
AS 
    BEGIN
        SET @string = REPLACE(@string, ',', '.')
        SET @string = (SELECT   SUBSTRING(@string, v.number, 1)
                       FROM     master..spt_values v
                       WHERE    v.type = 'P'
                                AND v.number BETWEEN 1 AND LEN(@string)
                                AND (SUBSTRING(@string, v.number, 1) LIKE '[0-9]'
                                     OR SUBSTRING(@string, v.number, 1) LIKE '[.]')
                       ORDER BY v.number
                      FOR
                       XML PATH('')
                      )
        RETURN @string
    END
GO

Thanks RichardTheKiwi +1

Select query to remove non-numeric characters value and get top value in SQL

You can achieve by this

select 
Max(CONVERT(int, stuff(Round, 1, patindex('%[0-9]%', Round)-1, '')))
from Table_LKP_RoundInfo

Remove Non Numeric Values Within SQL Select Statement

If you want to get the number at the end of the string, you can use the following arcane approach:

select reverse(reverse(value) + 0) as NumberAtEnd;

In your case:

• value ='C_4327'
• reverse(value) = '7234_C'
• reverse(value) + 0 = 7234
• reverse(reverse(value) + 0) = '4327'

---

Related Topics