quitar - sql substring hasta un caracter

La forma más rápida de eliminar caracteres no numéricos de un VARCHAR en SQL Server

Question

Estoy escribiendo una utilidad de importación que usa números de teléfono como una clave única dentro de la importación.

Necesito verificar que el número de teléfono no exista en mi base de datos. El problema es que los números de teléfono en la base de datos pueden tener elementos como guiones y paréntesis y posiblemente otras cosas. Escribí una función para eliminar estas cosas, el problema es que es lenta y con miles de registros en mi base de datos y miles de registros para importar a la vez, este proceso puede ser inaceptablemente lento. Ya he hecho que la columna del número de teléfono sea un índice.

Intenté usar el script de esta publicación:
T-SQL trim & nbsp (y otros caracteres no alfanuméricos)

Pero eso no lo aceleró ninguno.

¿Hay una forma más rápida de eliminar los caracteres no numéricos? Algo que puede funcionar bien cuando se tienen que comparar 10,000 a 100,000 registros.

Lo que sea que se haga, necesita actuar rápido .

Actualizar
Dado lo que la gente respondió, creo que tendré que limpiar los campos antes de ejecutar la utilidad de importación.

Para responder a la pregunta de en qué estoy escribiendo la utilidad de importación, se trata de una aplicación C #. Estoy comparando BIGINT con BIGINT ahora, sin necesidad de alterar los datos de la base de datos y todavía estoy teniendo un golpe de rendimiento con un conjunto muy pequeño de datos (alrededor de 2000 registros).

¿Podría la comparación de BIGINT a BIGINT ralentizar las cosas?

Optimicé el lado del código de mi aplicación tanto como puedo (elimina expresiones regulares, elimina llamadas DB innecesarias). Aunque ya no puedo aislar SQL como la fuente del problema, todavía me siento como si lo fuera.

Answer 1

"Aunque ya no puedo aislar SQL como la fuente del problema, todavía me siento como si fuera así".

Enciende SQL Profiler y échale un vistazo. Tome las consultas resultantes y verifique sus planes de ejecución para asegurarse de que se está utilizando el índice.

Answer 2

¿Puedes eliminarlos en un proceso nocturno, almacenarlos en un campo separado, y luego hacer una actualización de los registros modificados justo antes de ejecutar el proceso?

O en la inserción / actualización, almacene el formato "numérico", para referencia posterior. Un disparador sería una manera fácil de hacerlo.

Answer 3

Buscando una solución súper simple:

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

Answer 4

En caso de que no desee crear una función, o necesite una sola llamada en línea en T-SQL, podría intentar:

set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,''('',''''),'' '',''''),''-'',''''),'')'','''')

Por supuesto, esto es específico para eliminar el formato del número de teléfono, no es un genérico eliminar todos los caracteres especiales de la función de cadena.

Answer 5

Función 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

Answer 6

Miles de registros contra miles de registros normalmente no son un problema. Utilicé SSIS para importar millones de registros con este modo.

Limpiaría la base de datos para eliminar los caracteres no numéricos en primer lugar y los mantendría fuera.

Answer 7

Primero probaría la función CLR de Scott, pero agregaría una cláusula WHERE para reducir el número de registros actualizados.

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber) 
WHERE phonenumber like ''%[^0-9]%''

Si sabes que la gran mayoría de tus registros tienen caracteres no numéricos, puede que no ayuden.

Answer 8

Puedo malinterpretar, pero tienes dos conjuntos de datos para eliminar las cadenas de uno para los datos actuales en la base de datos y luego un nuevo conjunto cada vez que importas.

Para actualizar los registros existentes, solo usaría SQL, eso solo tiene que suceder una vez.

Sin embargo, SQL no está optimizado para este tipo de operación, ya que usted dijo que está escribiendo una utilidad de importación, yo haría esas actualizaciones en el contexto de la utilidad de importación, no en SQL. Esto sería mucho mejor en cuanto a rendimiento. ¿En qué estás escribiendo la utilidad?

Además, es posible que esté malinterpretando por completo el proceso, por lo que me disculpo si fuera de la base.

Editar:
Para la actualización inicial, si está utilizando SQL Server 2005, puede probar una función CLR. Aquí hay uno rápido usando regex. No estoy seguro de cómo se compararía el rendimiento. Nunca he usado esto, excepto por una prueba rápida en este momento.

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, "");  
    }  
};  

Después de implementarlo, para actualizar puede usar:

UPDATE table SET phoneNumber = dbo.StripNonNumeric(phoneNumber)

Answer 9

Recomendaría imponer un formato estricto para los números de teléfono en la base de datos. Yo uso el siguiente formato. (Asumiendo los números de teléfono de los EE. UU.)

Base de datos: 5555555555x555

Pantalla: [phone] ext 555

Entrada: 10 dígitos o más dígitos incrustados en cualquier cadena. (El reemplazo de Regex elimina todos los caracteres no numéricos)

Answer 10

Sé que es tarde para el juego, pero esta es una función que creé para T-SQL que elimina rápidamente los caracteres no numéricos. De nota, tengo un esquema "String" que puse funciones de utilidad para cadenas en ...

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

Luego usarlo para comparar para insertar, algo como esto;

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

Answer 11

Trabajar con varchar es fundamentalmente lento e ineficiente en comparación con trabajar con numéricos, por razones obvias. Las funciones a las que se vincula en la publicación original serán bastante lentas, ya que recorrerán cada carácter de la cadena para determinar si es un número o no. Hazlo por miles de registros y el proceso será lento. Este es el trabajo perfecto para Regular Expressions, pero no son compatibles de forma nativa en SQL Server. Puede agregar soporte usando una función CLR, pero es difícil decir qué tan lento será sin intentarlo ¡Definitivamente esperaría que fuera significativamente más rápido que recorrer cada carácter de cada número de teléfono!

Una vez que obtenga los números de teléfono formateados en su base de datos para que sean solo números, podría cambiar a un tipo numérico en SQL que arrojaría comparaciones rápidas contra otros tipos numéricos. Puede encontrar que, dependiendo de qué tan rápido ingresen sus nuevos datos, hacer el recorte y la conversión a numérico en el lado de la base de datos es lo suficientemente rápido una vez que lo que está comparando está formateado correctamente, pero si es posible, sería mejor escribiendo una utilidad de importación en un lenguaje .NET que se ocuparía de estos problemas de formateo antes de llegar a la base de datos.

De cualquier manera, tendrá un gran problema con el formato opcional. Incluso si se garantiza que sus números serán solo de origen norteamericano, algunas personas colocarán el 1 en frente de un número de teléfono calificado con un código de área completo y otras no, lo que causará la posibilidad de entradas múltiples del mismo número de teléfono. Además, dependiendo de lo que represente su información, algunas personas usarán su número de teléfono particular, que podría tener varias personas viviendo allí, por lo que una restricción única en el mismo solo permitiría un miembro de la base de datos por hogar. Algunos usarían su número de trabajo y tendrían el mismo problema, y ​​algunos incluirían o no incluirían la extensión que causaría potencial de exclusividad artificial nuevamente.

Todo eso puede o no afectarlo, dependiendo de sus datos y usos particulares, ¡pero es importante tenerlo en cuenta!

Answer 12

Utilizaría una función en línea desde la perspectiva del rendimiento, ver a continuación: tenga en cuenta que los símbolos como ''+'', ''-'' etc. no se eliminarán

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

Puedes definirlo con más de 100 caracteres ...

Answer 13

Vi esta solución con el código T-SQL y PATINDEX. Me gusta :-)

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

Answer 14

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 ,

:)

Answer 15

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%^@#$^'')