Consulta de selección T-SQL para eliminar caracteres no numéricos

Tengo datos sucios en una columna con longitud alfa variable. Solo quiero quitar todo lo que no sea 0-9.

No quiero ejecutar una función o proceso. Tengo un script que es similar y que simplemente toma el valor numérico después del texto, se ve así:

Update TableName set ColumntoUpdate=cast(replace(Columnofdirtydata,'Alpha #','') as int) where Columnofdirtydata like 'Alpha #%' And ColumntoUpdate is Null 

Pensé que funcionaría bastante bien hasta que descubrí que algunos de los campos de datos que creía que estarían en el formato Alfa # 12345789 no son …

Ejemplos de datos que deben ser eliminados

 AB ABCDE # 123 ABCDE# 123 AB: ABC# 123 

Solo quiero el 123. Es verdad que todos los campos de datos tienen el # antes del número.

Intenté subcadena y PatIndex, pero no estoy obteniendo la syntax correcta o algo así. ¿Alguien tiene algún consejo sobre la mejor manera de abordar esto?

¡Gracias!

Vea esta publicación en el blog sobre la extracción de números de cadenas en SQL Server. A continuación se muestra una muestra con una cadena en su ejemplo:

 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) 

Puedes usar cosas y patindex .

 stuff(Col, 1, patindex('%[0-9]%', Col)-1, '') 

SQL Fiddle

En caso de que haya algunos caracteres posibles entre los dígitos (por ejemplo, separadores de miles), puede intentar lo siguiente:

 declare @table table (DirtyCol varchar(100)) insert into @table values ('AB ABCDE # 123') ,('ABCDE# 123') ,('AB: ABC# 123') ,('AB#') ,('AB # 1 000 000') ,('AB # 1`234`567') ,('AB # (9)(876)(543)') ;with tally as (select top (100) N=row_number() over (order by @@spid) from sys.all_columns), data as ( select DirtyCol, Col from @table cross apply ( select (select C + '' from (select N, substring(DirtyCol, N, 1) C from tally where N< =datalength(DirtyCol)) [1] where C between '0' and '9' order by N for xml path('')) ) p (Col) where p.Col is not NULL ) select DirtyCol, cast(Col as int) IntCol from data 

La salida es:

 DirtyCol IntCol --------------------- ------- AB ABCDE # 123 123 ABCDE# 123 123 AB: ABC# 123 123 AB # 1 000 000 1000000 AB # 1`234`567 1234567 AB # (9)(876)(543) 9876543 

Para la actualización, agregue ColToUpdate para seleccionar la lista de data cte:

 ;with num as (...), data as ( select ColToUpdate, /*DirtyCol, */Col from ... ) update data set ColToUpdate = cast(Col as int) 

Esto funciona bien para mi:

 CREATE FUNCTION [dbo].[StripNonNumerics] ( @Temp varchar(255) ) RETURNS varchar(255) AS Begin Declare @KeepValues as varchar(50) Set @KeepValues = '%[^0-9]%' While PatIndex(@KeepValues, @Temp) > 0 Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '') Return @Temp End 

A continuación, llame a la función como para ver algo original junto a algo desinfectado:

 SELECT Something, dbo.StripNonNumerics(Something) FROM TableA 

Aquí hay una solución elegante si su servidor admite la función TRANSLATE (en el servidor sql está disponible en el servidor sql 2017+ y también sql azul).

Primero, reemplaza cualquier carácter no numérico con un carácter @. Luego, elimina todos los @ caracteres. Es posible que necesite agregar caracteres adicionales que sabe que pueden estar presentes en el segundo parámetro de la llamada TRANSLATE.

 select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '') 

Para agregar a la respuesta de Ken , maneja comas y espacios y paréntesis

 --Handles parentheses, commas, spaces, hyphens.. declare @table table (c varchar(256)) insert into @table values ('This is a test 111-222-3344'), ('Some Sample Text (111)-222-3344'), ('Hello there 111222 3344 / How are you?'), ('Hello there 111 222 3344 ? How are you?'), ('Hello there 111 222 3344. How are you?') select replace(LEFT(SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000), PATINDEX('%[^0-9.-]%', SUBSTRING(replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',',''), PATINDEX('%[0-9.-]%', replace(replace(replace(replace(replace(c,'(',''),')',''),'-',''),' ',''),',','')), 8000) + 'X') -1),'.','') from @table 
  Declare @MainTable table(id int identity(1,1),TextField varchar(100)) INSERT INTO @MainTable (TextField) VALUES ('6B32E') declare @i int=1 Declare @originalWord varchar(100)='' WHile @i< =(Select count(*) from @MainTable) BEGIN Select @originalWord=TextField from @MainTable where id=@i Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0 Select @len = len(@originalWord) declare @pn varchar(100)=@originalWord while @x <= @len begin Select @c = SUBSTRING(@pn,@x,1) if(@c!='') BEGIN if ISNUMERIC(@c) = 0 and @c <> '-' BEGIN Select @r = cast(@r as varchar) + cast(replace((SELECT ASCII(@c)-64),'-','') as varchar) end ELSE BEGIN Select @r = @r + @c END END Select @x = @x +1 END Select @r Set @i=@i+1 END 

Aquí hay una versión que saca todos los dígitos de una cadena; es decir, dado I'm 35 years old; I was born in 1982. The average family has 2.4 children. I'm 35 years old; I was born in 1982. The average family has 2.4 children. esto devolvería 35198224 . es decir, es bueno que tenga datos numéricos que pueden haber sido formateados como un código (por ejemplo, #123,456,789 / 123-00005 ), pero no es apropiado si está buscando sacar números específicos (es decir, en oposición a los dígitos / solo los caracteres numéricos) del texto. También solo maneja dígitos; por lo tanto, no devolverá signos negativos ( - ) o puntos . )

 declare @table table (id bigint not null identity (1,1), data nvarchar(max)) insert @table (data) values ('hello 123 its 45613 then') --outputs: 12345613 ,('1 some other string 98 example 4') --outputs: 1984 ,('AB ABCDE # 123') --outputs: 123 ,('ABCDE# 123') --outputs: 123 ,('AB: ABC# 123') --outputs: 123 ; with NonNumerics as ( select id , data original --the below line replaces all digits with blanks , replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(data,'0',''),'1',''),'2',''),'3',''),'4',''),'5',''),'6',''),'7',''),'8',''),'9','') nonNumeric from @table ) --each iteration of the below CTE removes another non-numeric character from the original string, putting the result into the numerics column , Numerics as ( select id , replace(original, substring(nonNumeric,1,1), '') numerics , replace(nonNumeric, substring(nonNumeric,1,1), '') charsToreplace , len(replace(nonNumeric, substring(nonNumeric,1,1), '')) charsRemaining from NonNumerics union all select id , replace(numerics, substring(charsToreplace,1,1), '') numerics , replace(charsToreplace, substring(charsToreplace,1,1), '') charsToreplace , len(replace(charsToreplace, substring(charsToreplace,1,1), '')) charsRemaining from Numerics where charsRemaining > 0 ) --we select only those strings with `charsRemaining=0`; ie the rows for which all non-numeric characters have been removed; there should be 1 row returned for every 1 row in the original data set. select * from Numerics where charsRemaining = 0 

Este código funciona eliminando todos los dígitos (es decir, los caracteres que queremos) de una cadena dada, reemplazándolos por espacios en blanco. Luego pasa por la cadena original (que incluye los dígitos) eliminando todos los caracteres que quedaron (es decir, los caracteres no numéricos), dejando solo los dígitos.

La razón por la que hacemos esto en 2 pasos, en lugar de simplemente eliminar todos los caracteres no numéricos, es que solo hay 10 dígitos, mientras que hay una gran cantidad de caracteres posibles; entonces, reemplazar esa pequeña lista es relativamente rápido; luego nos da una lista de los caracteres no numéricos que realmente existen en la cadena, por lo que podemos reemplazar ese pequeño conjunto.

El método hace uso de SQL recursivo, usando expresiones de tabla comunes (CTE).

He creado una función para esto

 Create FUNCTION RemoveCharacters (@text varchar(30)) RETURNS VARCHAR(30) AS BEGIN declare @index as int declare @newtexval as varchar(30) set @index = (select PATINDEX('%[AZ.-/?]%', @text)) if (@index =0) begin return @text end else begin set @newtexval = (select STUFF ( @text , @index , 1 , '' )) return dbo.RemoveCharacters(@newtexval) end return 0 END GO 

Aquí está la respuesta:

 DECLARE @t TABLE (tVal VARCHAR(100)) INSERT INTO @t VALUES('123') INSERT INTO @t VALUES('123S') INSERT INTO @t VALUES('A123,123') INSERT INTO @t VALUES('a123..A123') ;WITH cte (original, tVal, n) AS ( SELECT t.tVal AS original, LOWER(t.tVal) AS tVal, 65 AS n FROM @t AS t UNION ALL SELECT tVal AS original, CAST(REPLACE(LOWER(tVal), LOWER(CHAR(n)), '') AS VARCHAR(100)), n + 1 FROM cte WHERE n < = 90 ) SELECT t1.tVal AS OldVal, t.tval AS NewVal FROM ( SELECT original, tVal, ROW_NUMBER() OVER(PARTITION BY tVal + original ORDER BY original) AS Sl FROM cte WHERE PATINDEX('%[az]%', tVal) = 0 ) t INNER JOIN @t t1 ON t.original = t1.tVal WHERE t.sl = 1 

Esto funcionó para mí:

  1. Eliminé las comillas simples.

  2. Luego usé un reemplazo "," con "." .

Sin duda, esto ayudará a alguien:

 " & txtFinalscore.Text.Replace(",", ".") & " 
 Create function fn_GetNumbersOnly(@pn varchar(100)) Returns varchar(max) AS BEGIN Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0 Select @len = len(@pn) while @x < = @len begin Select @c = SUBSTRING(@pn,@x,1) if ISNUMERIC(@c) = 1 and @c <> '-' Select @r = @r + @c Select @x = @x +1 end return @r End