Consulta de selección de T-SQL para eliminar caracteres no numéricos
Tengo datos sucios en una columna con longitud alfa variable. Solo quiero desnudar cualquier cosa que no sea 0-9.
No quiero ejecutar una función o proc. Tengo un script que es similar que solo agarra 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 pensé que solo estarían en el formato Alpha # 12345789 no lo son...
Ejemplos de datos que deben ser stripped
AB ABCDE # 123
ABCDE# 123
AB: ABC# 123
Solo quiero el 123. Es cierto que todos los campos de datos tienen el # antes del número.
He intentado subcadena y PatIndex, pero no estoy consiguiendo bastante la sintaxis correcta o algo así. Alguien tiene algún consejo sobre la mejor manera de abordar esto?
¡Gracias!
12 answers
Vea esta entrada de blog sobre la extracción de números de cadenas en SQL Server. A continuación se muestra un ejemplo usando 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)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-09-04 23:52:21
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-09-05 05:19:23
En caso de que haya algunos caracteres posibles entre 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 del data
cte:
;with num as (...),
data as (
select ColToUpdate, /*DirtyCol, */Col
from ...
)
update data
set ColToUpdate = cast(Col as int)
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-09-05 11:40:21
Esto funciona bien para mí:
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
Luego llame a la función de esta manera para ver el algo original junto al algo desinfectado:
SELECT Something, dbo.StripNonNumerics(Something) FROM TableA
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-12-08 14:03:49
Aquí hay una solución elegante si su servidor admite la función TRANSLATE (en sql server está disponible en sql server 2017+ y también en sql azure).
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 sepa que pueden estar presentes en el segundo parámetro de la llamada TRANSLATE.
select REPLACE(TRANSLATE([Col], 'abcdefghijklmnopqrstuvwxyz+()- ,#+', '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@'), '@', '')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-09-03 07:06:57
Para agregar a la respuesta de Ken , esto 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-07-27 16:15:42
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-11-22 10:13:25
Aquí hay una versión que extrae 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.
esto devolvería 35198224
. es decir, es bueno cuando tienes datos numéricos que pueden haber sido formateados como un código (p.ej. #123,456,789
/ 123-00005
), pero no es apropiado si está buscando extraer números específicos (es decir, en lugar de dígitos / solo los caracteres numéricos) del texto. También solo maneja dígitos; por lo que 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`; i.e. 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 las cadenas dadas reemplazándolos con 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 en primer lugar, es que solo hay 10 dígitos, mientras que hay un gran número de caracteres posibles; por lo que reemplazar ese pequeño list es relativamente rápido; luego nos da una lista de esos caracteres no numéricos que realmente existen en la cadena, para que luego podamos reemplazar ese pequeño conjunto.
El método hace uso de SQL recursivo, utilizando expresiones de tabla comunes (CTE).
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-15 10:57:13
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('%[A-Z.-/?]%', @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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-04 16:35:28
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('%[a-z]%', tVal) = 0
) t
INNER JOIN @t t1
ON t.original = t1.tVal
WHERE t.sl = 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-12 07:18:52
Esto funcionó para mí:
Eliminé las comillas simples.
Luego usé un reemplazo
","
con"."
.
Seguramente esto ayudará a alguien:
" & txtFinalscore.Text.Replace(",", ".") & "
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-16 13:15:53
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-12-27 07:24:58