Schnellste Weg, um entfernen nicht-numerische Zeichen von VARCHAR in SQL Server

Schreibe ich eine import-Dienstprogramm, das verwenden von Telefonnummern, wie Sie einen eindeutigen Schlüssel innerhalb der import.

Ich brauche, um zu überprüfen, dass die Telefonnummer nicht bereits in meiner DB. Das problem ist, dass die Telefonnummern in der DB hätten die Dinge wie Bindestriche und Klammern, und möglicherweise auch andere Dinge. Ich schrieb eine Funktion zum entfernen dieser Dinge, das problem ist, dass es langsam und mit tausenden von Datensätze in meiner DB und Tausende von Datensätzen zu importieren, auf einmal, kann dieser Prozess sehr langsam. Hab ich schon gemacht und der Telefonnummer-Spalte ein index ist.

Ich habe versucht mit dem script aus diesem post:
T-SQL-trim &nbsp (und andere nicht-alphanumerische Zeichen)

Aber das hat nicht beschleunigen alle.

Ist es ein schneller Weg, um zu entfernen nicht-numerische Zeichen? Etwas, das gut performen kann, wenn 10.000 bis 100.000 Datensätze verglichen werden müssen.

Was auch immer getan durchführen muss schnell.

Update
Angesichts dessen, was Menschen reagierten darauf mit, ich glaube, ich bin zu haben, reinigen Sie die Felder aus, bevor ich das import-Dienstprogramm.

Antwort auf die Frage, was Schreibe ich das import-Dienstprogramm, es ist eine C# - app. Ich Vergleiche BIGINT, BIGINT jetzt, keine Notwendigkeit zu ändern, DB-Daten und ich bin immer noch unter einen performance-hit mit einer sehr kleinen Menge von Daten (über 2000 Datensätze).

Konnte Vergleich von BIGINT bis BIGINT werden verlangsamt Dinge nach unten?

Habe ich optimiert den code der Seite meiner app so viel wie ich kann (entfernt regexes, entfernt unnötige DB-Aufrufe). Zwar kann ich nicht isolieren SQL als Quelle der problem mehr, ich fühle mich immer noch wie es ist.

InformationsquelleAutor Dan Herbert | 2008-09-19

16

Ich kann es missverstehen, aber du hast zwei Sätze von Daten, um entfernen Sie die Saiten von einem für die aktuellen Daten in der Datenbank und dann einen neuen Satz, wenn Sie importieren.

Zur Aktualisierung der bestehenden Datensätze, ich möchte nur SQL verwenden, die nur einmal geschehen.

Aber SQL ist nicht optimiert für diese Art der operation, da Sie sagten, Sie schreiben eine import-Dienstprogramm, die ich tun würde, diese updates im Rahmen der import-Dienstprogramm selbst, nicht in SQL. Dies wäre viel besser Leistung klug. Was schreiben Sie das Programm in?

Auch, ich kann ganz Missverständnis des Prozesses, so dass ich entschuldige mich, wenn off-base.

Edit:

Für das erste update, wenn Sie SQL Server 2005 verwenden, könnten Sie versuchen, eine CLR-Funktion. Hier ist auf die schnelle mit regex. Nicht sicher, wie Sie die Leistung vergleichen, habe ich diesen noch nie benutzt habe mich außer für einen schnellen test jetzt.
```
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, "");  
    }  
};  
```
Nachdem diese bereitgestellt wird, zu aktualisieren, Sie könnten einfach verwenden:
```
UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)
```
InformationsquelleAutor Scott Nichols

103

Sah ich diese Lösung mit T-SQL-code und PATINDEX. Ich mag es 🙂

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END

InformationsquelleAutor David Coster

36

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(string,'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','')*1 AS string,

🙂

Sie vergessen ( ) - # usw.
* CAST(1 AS bigint)

InformationsquelleAutor Brainwater
16

Fall, dass Sie nicht wollen, um eine Funktion zu erstellen, oder Sie brauchte nur einen einzigen inline-call-in T-SQL, die Sie könnten versuchen:
```
set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'(',''),' ',''),'-',''),')','')
```
Natürlich ist das spezifisch für das entfernen von Telefonnummer formatieren, nicht eine generische entfernen Sie alle Sonderzeichen aus dem string-Funktion.

InformationsquelleAutor Tom

Einfache Funktion:

CREATE FUNCTION [dbo].[RemoveAlphaCharacters](@InputString VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
  WHILE PATINDEX('%[^0-9]%',@InputString)>0
        SET @InputString = STUFF(@InputString,PATINDEX('%[^0-9]%',@InputString),1,'')     
  RETURN @InputString
END

GO

InformationsquelleAutor AdamE

create function dbo.RemoveNonNumericChar(@str varchar(500))  
returns varchar(500)  
begin  
declare @startingIndex int  
set @startingIndex=0  
while 1=1  
begin  
    set @startingIndex= patindex('%[^0-9]%',@str)  
    if @startingIndex <> 0  
    begin  
        set @str = replace(@str,substring(@str,@startingIndex,1),'')  
    end  
    else    break;   
end  
return @str  
end

go  

select dbo.RemoveNonNumericChar('aisdfhoiqwei352345234@#$%^$@345345%^@#$^')

InformationsquelleAutor Debayan Samaddar

1

können Sie entfernen Sie Sie in einem nächtlichen Prozess, der Speicherung in einem eigenen Feld, dann tun Sie ein update auf die geänderten Datensätze nach rechts, bevor Sie den Vorgang ausführen?

Oder auf einfügen/aktualisieren, speichern Sie die "numeric" - format, Referenz später. Ein Auslöser wäre eine einfache Möglichkeit, es zu tun.

InformationsquelleAutor Dan Williams
1

Ich würde versuchen, Scott CLR-Funktion den ersten, aber eine WHERE-Klausel hinzufügen, reduzieren Sie die Anzahl der Datensätze, die aktualisiert werden.
```
UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber) 
WHERE phonenumber like '%[^0-9]%'
```
Wenn Sie wissen, dass die große Mehrheit der Datensätze mit nicht-numerischer Zeichen, es kann nicht helfen, aber.

InformationsquelleAutor Mike L

Ich weiß, es ist spät, um das Spiel, aber hier ist eine Funktion, die ich erstellt für T-SQL, entfernt schnell nicht-numerische Zeichen. Der Hinweis, ich habe ein schema "String" , ich legte utility-Funktionen für strings in...

CREATE FUNCTION String.ComparablePhone( @string nvarchar(32) ) RETURNS bigint AS
BEGIN
    DECLARE @out bigint;

-- 1. table of unique characters to be kept
    DECLARE @keepers table ( chr nchar(1) not null primary key );
    INSERT INTO @keepers ( chr ) VALUES (N'0'),(N'1'),(N'2'),(N'3'),(N'4'),(N'5'),(N'6'),(N'7'),(N'8'),(N'9');

-- 2. Identify the characters in the string to remove
    WITH found ( id, position ) AS
    (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY (n1+n10) DESC), -- since we are using stuff, for the position to continue to be accurate, start from the greatest position and work towards the smallest
            (n1+n10)
        FROM 
            (SELECT 0 AS n1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS d1,
            (SELECT 0 AS n10 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) AS d10
        WHERE
            (n1+n10) BETWEEN 1 AND len(@string)
            AND substring(@string, (n1+n10), 1) NOT IN (SELECT chr FROM @keepers)
    )
-- 3. Use stuff to snuff out the identified characters
    SELECT 
        @string = stuff( @string, position, 1, '' )
    FROM 
        found
    ORDER BY
        id ASC; -- important to process the removals in order, see ROW_NUMBER() above

-- 4. Try and convert the results to a bigint   
    IF len(@string) = 0
        RETURN NULL; -- an empty string converts to 0

    RETURN convert(bigint,@string); 
END

Dann zu vergleichen, die für das einfügen, so etwas wie dieses;

INSERT INTO Contacts ( phone, first_name, last_name )
SELECT i.phone, i.first_name, i.last_name
FROM Imported AS i
LEFT JOIN Contacts AS c ON String.ComparablePhone(c.phone) = String.ComparablePhone(i.phone)
WHERE c.phone IS NULL -- Exclude those that already exist

InformationsquelleAutor Dennis Allen

1

Arbeiten mit varchars ist grundsätzlich langsam und ineffizient im Vergleich zu der Arbeit mit Numerik, aus offensichtlichen Gründen. Die Funktionen, die Sie ein link in der original-Beitrag ist in der Tat ziemlich langsam, wie Sie eine Schleife durch jedes Zeichen in der Zeichenfolge, um zu bestimmen, ob oder nicht es ist eine Zahl. Tun, dass für Tausende von Datensätzen und den Prozess gebunden ist, langsam zu sein. Dies ist der perfekte job für Reguläre Ausdrücke, aber Sie sind nicht nativ in SQL Server unterstützt. Können Sie Unterstützung hinzufügen, verwenden Sie eine CLR-Funktion, aber es ist schwer zu sagen, wie langsam das sein wird, ohne zu versuchen, Sie ich würde auf jeden Fall erwarten, dass es deutlich schneller als die Schleife durch jedes Zeichen von jedem Telefon-Nummer, aber!

Sobald man das Telefon formatierte zahlen in Ihrer Datenbank, so dass Sie nur zahlen, Sie wechseln konnte, in einen numerischen Datentyp in SQL würde die Ausbeute Blitz-schnell Vergleiche mit anderen numerischen Typen. Sie werden feststellen, dass, je nachdem, wie schnell Ihr neue Daten ankommen, tut das trimmen und Umwandlung von numerischen auf der Datenbank-Seite ist reichlich schnell genug, wenn das, was Sie sind im Vergleich zu den richtig formatiert, aber wenn möglich, wäre es besser zu schreiben, ein import-Programm in einem .NET-Sprache, die kümmern sich um diese Probleme mit der Formatierung, bevor Sie auf die Datenbank.

Entweder Weg, obwohl, Sie gehen zu müssen, ein großes problem in Bezug auf optionale Formatierung. Auch wenn Ihre zahlen sind garantiert nur nordamerikanischen Ursprungs, einige Leute setzen die 1 vor einem komplett area-code qualifizierten Telefonnummer und andere nicht, wodurch sich das Potenzial für mehrere Einträge von der gleichen Telefonnummer. Außerdem, je nachdem, was Ihre Daten darstellt, einige Leute werden über Ihre private Telefonnummer, die möglicherweise mehrere Menschen, die dort Leben, also eine unique-Einschränkung auf würde es erlauben nur eine Datenbank-Mitglied pro Haushalt. Einige verwenden würden, Ihre Arbeit, die Anzahl und das gleiche problem haben, und manche würden nicht oder gehören die Erweiterung, die Ursache wäre künstliche Einzigartigkeit Potenzial wieder.

Alle, die möglicherweise oder möglicherweise keine Auswirkungen auf Sie, je nach Ihren spezifischen Daten und Verwendungen, aber es ist wichtig, im Auge zu behalten!

InformationsquelleAutor Grank
0

"Zwar kann ich nicht isolieren SQL als Quelle der problem mehr, ich fühle mich immer noch wie es ist."

Feuer bis Sie SQL Profiler und schauen. Die resultierende Abfragen und überprüfen die Ausführung von Plänen, um sicherzustellen, dass der index verwendet wird.

InformationsquelleAutor Amy B
0

Tausende von Datensätzen gegen Tausende von Datensätzen ist normalerweise kein problem. Ich habe SSIS import von Millionen von Datensätzen mit de-duping wie diese.

Ich würde bereinigen der Datenbank zu entfernen, die nicht-numerische Zeichen in der ersten Stelle, und halten Sie Sie heraus.

InformationsquelleAutor Cade Roux

Suchen für eine super-einfache Lösung:

SUBSTRING([Phone], CHARINDEX('(', [Phone], 1)+1, 3)
       + SUBSTRING([Phone], CHARINDEX(')', [Phone], 1)+1, 3)
       + SUBSTRING([Phone], CHARINDEX('-', [Phone], 1)+1, 4) AS Phone

InformationsquelleAutor Tim

Ich würde die Verwendung einer Inline-Funktion aus performance-Sicht, siehe unten:
Beachten Sie, dass Symbole wie '+','-' usw. werden nicht entfernt werden

CREATE FUNCTION [dbo].[UDF_RemoveNumericStringsFromString]
 (
 @str varchar(100)
 )
 RETURNS TABLE AS RETURN
 WITH Tally (n) as 
  (
  -- 100 rows
   SELECT TOP (Len(@Str)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
   CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
  )

  SELECT OutStr =  STUFF(
       (SELECT SUBSTRING(@Str, n,1) st
        FROM Tally
        WHERE ISNUMERIC(SUBSTRING(@Str, n,1)) = 1
        FOR XML PATH(''),type).value('.', 'varchar(100)'),1,0,'')
  GO

  /*Use it*/
  SELECT OutStr
  FROM dbo.UDF_RemoveNumericStringsFromString('fjkfhk759734977fwe9794t23')
  /*Result set
   759734977979423 */

Können Sie definieren es mit mehr als 100 Zeichen...

InformationsquelleAutor hkravitz

-1

Ich würde empfehlen, die Durchsetzung eines strengen format für Telefonnummern in der Datenbank. Ich benutze das folgende format. (Vorausgesetzt, dass UNS die Telefon-Nummern)

Datenbank: 5555555555x555

Anzeige: (555) 555-5555 ext 555

Input: 10-stellig oder mehr Ziffern, eingebettet in eine beliebige Zeichenfolge. (Regex ersetzen entfernt alle nicht-numerischen Zeichen)

InformationsquelleAutor epochwolf

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.