Fastest Way to Remove Non-Numeric Characters from a Varchar in SQL Server

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)

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 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

SQL Script that removes all non-numeric numbers

As you only have a few problem characters you can just do a nested replace.

UPDATE T
SET PhoneNumber = REPLACE(REPLACE(REPLACE(PhoneNumber,'(',''),')',''),'-','')

_{See REPLACE()}

Removing non-numeric characters in T-SQL

REPLACE(column, 'KB', ''). No need for LEN and other stuff

On SQL 2005, this will give you the "reserved" value:

SELECT
    SUM(au.total_pages) / 128.0 AS UsedMB
FROM
    sys.allocation_units au

Some more investigation should allow you to read index vs data space out of the catlog views too

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

Removing strings from a varchar column in SQL server

You can try this:

DECLARE @temp TABLE
(
    string NVARCHAR(50)
)

INSERT INTO @temp (string)
VALUES 
    ('123abc'),
    ('ab12c'),
    ('ab45c?')

SELECT SUBSTRING(string, PATINDEX('%[0-9]%', string), PATINDEX('%[0-9][^0-9]%', string + 't') - PATINDEX('%[0-9]%', 
                    string) + 1) AS Number
FROM @temp

How to strip all non-alphabetic characters from string in SQL Server?

Try this function:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

Call it like this:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

Once you understand the code, you should see that it is relatively simple to change it to remove other characters, too. You could even make this dynamic enough to pass in your search pattern.

How to replace all non-numeric values in a varchar Column with a 0

It seems simple enough, you can just use try_cast and replace all non valid data with '0'

update t
set col1='0'
where try_cast(col1 as bigint) is null

---

Related Topics