La manière la plus rapide de supprimer des caractères non numériques d'un VARCHAR dans un serveur SQL
j'écris un utilitaire d'importation qui utilise des numéros de téléphone comme clé unique dans l'importation.
je dois vérifier que le numéro de téléphone n'existe pas déjà dans mon DB. Le problème est que les numéros de téléphone dans le DB pourraient avoir des choses comme des tirets et des parenthèses et peut-être d'autres choses. J'ai écrit une fonction pour supprimer ces choses, le problème est qu'il est lent et avec des milliers d'enregistrements dans ma DB et des milliers de dossiers à importer à la fois, ce processus peut être exagérément lente. J'ai déjà fait un index de la colonne des numéros de téléphone.
j'ai essayé d'utiliser le script de ce post:
T-SQL trim   (et autres caractères non alphanumériques)
mais ça ne l'a pas accéléré.
Existe-t-il un moyen plus rapide de supprimer les caractères non numériques? Quelque chose qui peut bien fonctionner quand 10 000 à 100 000 dossiers doivent être comparer.
tout ce qui est fait doit effectuer rapide .
mise à Jour
Vu la réaction des gens, je pense que je vais devoir nettoyer les champs avant de lancer l'import utility.
pour répondre à la question de ce que j'écris l'utilitaire d'importation, c'est un c# app. Je compare BIGINT à BIGINT maintenant, sans besoin de modifier DB data et je prends toujours un gain de performance avec un très petit ensemble de données (environ 2000 dossiers).
est-ce que comparer BIGINT à BIGINT pourrait ralentir les choses?
j'ai optimisé le côté code de mon application autant que je le peux (j'ai supprimé regexes, supprimé unneccessary DB calls). Bien que je ne puisse plus isoler SQL comme la source du problème, j'ai toujours l'impression que c'est le cas.
15 réponses
je peux mal comprendre, mais vous avez deux ensembles de données pour supprimer les chaînes d'un pour les données courantes dans la base de données et puis un nouvel ensemble chaque fois que vous importez.
pour mettre à jour les enregistrements existants, je n'utiliserais que SQL, ce qui ne doit se produire qu'une seule fois.
cependant, SQL N'est pas optimisé pour ce genre d'opération, puisque vous avez dit que vous écrivez un utilitaire d'importation, je ferais ces mises à jour dans le contexte de l'utilitaire d'importation lui-même, pas dans SQL. Ce serait beaucoup mieux la performance sage. Ce que vous écrivez l'utilité?
aussi, je peux être complètement incompréhensible le processus, donc je m'excuse si hors-base.
Edit:
Pour la mise à jour initiale, si vous utilisez SQL Server 2005, vous pouvez essayer une fonction CLR. En voici un rapide qui utilise regex. Je ne sais pas comment la performance se comparerait, Je ne l'ai jamais utilisé moi-même à l'exception d'un test rapide maintenant.
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, "");
}
};
après ce qui est déployé, pour mettre à jour vous pouvez juste utiliser:
UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)
j'ai vu cette solution avec le code T-SQL et le PATINDEX. Je l'aime :-)
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
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 ,
:)
dans le cas où vous ne vouliez pas créer une fonction, ou vous aviez besoin d'un seul appel inline dans T-SQL, vous pouvez essayer:
set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'(',''),' ',''),'-',''),')','')
bien sûr, Ceci est spécifique à la suppression du formatage du numéro de téléphone, pas un générique supprimer tous les caractères spéciaux de la fonction de chaîne de caractères.
fonction Simple:
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
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%^@#$^')
pouvez-vous les supprimer dans un processus de nuit, les stocker dans un champ séparé, puis faire une mise à jour sur les enregistrements modifiés juste avant d'exécuter le processus?
ou sur l'insert/mise à jour, stocker le format" numérique", pour référence ultérieure. Un déclencheur est un moyen facile de le faire.
j'essaierais D'abord la fonction CLR de Scott, mais j'ajouterais une clause WHERE pour réduire le nombre d'enregistrements mis à jour.
UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)
WHERE phonenumber like '%[^0-9]%'
si vous savez que la grande majorité de vos enregistrements ont des caractères non numériques, cela pourrait ne pas aider.
je sais qu'il est tard pour le jeu, mais voici une fonction que j'ai créé pour T-SQL qui supprime rapidement les caractères non numériques. A noter, j'ai un schéma "String" dans lequel j'ai mis des fonctions utilitaires pour les chaînes...
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
puis l'utiliser pour comparer pour insérer, quelque chose comme ceci;
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
travailler avec varchars est fondamentalement lent et inefficace comparé à travailler avec numerics, pour des raisons évidentes. Les fonctions que vous liez dans le post original sera en effet assez lent, car ils boucle à travers chaque caractère dans la chaîne de déterminer si oui ou non c'est un nombre. Faites-le pour des milliers de documents et le processus sera lent. C'est le travail parfait pour les Expressions régulières, mais elles ne sont pas nativement supportées dans SQL Server. Vous pouvez ajouter un support Fonction CLR, mais il est difficile de dire comment ralentir ce sera sans l'essayer, je voudrais certainement s'attendre à être nettement plus rapide qu'en parcourant chaque caractère de chaque numéro de téléphone, cependant!
une fois que vous obtenez les numéros de téléphone formatés dans votre base de données afin qu'ils soient seulement des numéros, vous pouvez passer à un type numérique en SQL qui donnerait des comparaisons rapides contre d'autres types numériques. Vous pourriez trouver cela, en fonction de la vitesse à laquelle vos nouvelles données sont à venir en, faisant le parage et la conversion numérique du côté de la base de données est assez rapide Une fois que ce que vous comparez est correctement formaté, mais si possible, vous seriez mieux d'écrire un utilitaire d'importation dans un langage .NET qui prendrait soin de ces problèmes de formatage avant de frapper la base de données.
quoi qu'il en soit, vous allez avoir un gros problème en ce qui concerne le formatage optionnel. Même si vos numéros ne sont garantis que D'origine nord-américaine, certains les gens placeront le 1 devant un numéro de téléphone entièrement qualifié et d'autres pas, ce qui causera la possibilité de Entrées multiples du même numéro de téléphone. En outre, selon ce que vos données représentent, certaines personnes utiliseront leur numéro de téléphone à domicile qui pourrait avoir plusieurs personnes vivant là, donc une contrainte unique sur elle ne permettrait qu'un seul membre de la base de données par ménage. Certains utiliseraient leur numéro de travail et auraient le même problème, et certains le feraient ou non. inclure l'extension qui entraînerait à nouveau un potentiel d'unicité artificielle.
tout cela peut ou ne peut pas vous affecter, en fonction de vos données et usages particuliers, mais il est important de garder à l'esprit!
je recommande l'application d'un format strict pour les numéros de téléphone dans la base de données. J'utilise le format suivant. (En supposant des numéros de téléphone américains)
base de données: 55555555x555
Display: (555) 555-5555 ext 555
Entrée: 10 chiffres ou plus chiffres inclus dans une chaîne de caractères. (Regex replacing supprime tous les caractères non numériques)
" même si Je ne peux plus isoler SQL comme source du problème, je me sens comme si c'était le cas."
allume le profileur SQL et jette un coup d'oeil. Prenez les requêtes résultantes et vérifiez leurs plans d'exécution pour vous assurer que index est utilisé.
des milliers de documents contre des milliers de documents n'est normalement pas un problème. J'ai utilisé le SSIS pour importer des millions de disques avec de-duper comme ça.
Je nettoierais la base de données pour supprimer les caractères non numériques en premier lieu et les garder hors.
à la recherche d'une solution très simple:
SUBSTRING([Phone], CHARINDEX('(', [Phone], 1)+1, 3)
+ SUBSTRING([Phone], CHARINDEX(')', [Phone], 1)+1, 3)
+ SUBSTRING([Phone], CHARINDEX('-', [Phone], 1)+1, 4) AS Phone
j'utiliserais une fonction en ligne du point de vue de la performance, voir ci-dessous: notez que les symboles comme"+", " - "etc. ne seront pas supprimés
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 */
, Vous pouvez le définir avec plus de 100 caractères...