T-SQL seleziona query per rimuovere caratteri non numerici

Ho dati sporchi in una colonna con lunghezza alfa variabile. Voglio solo rimuovere tutto ciò che non è 0-9.

Non voglio eseguire una funzione o proc. Ho uno script che è simile che cattura il valore numerico dopo il testo, sembra così:

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

Ho pensato che avrebbe funzionato abbastanza bene fino a quando ho scoperto che alcuni dei campi dati che pensavo sarebbero solo nel formato Alpha # 12345789 non sono …

Esempi di dati che devono essere rimossi

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

Voglio solo il 123. È vero che tutti i campi di dati hanno il # prima del numero.

Ho provato la sottostringa e PatIndex, ma non sto ottenendo la syntax corretta o qualcosa del genere. Qualcuno ha qualche consiglio sul modo migliore per affrontare questo?

Grazie!

Vedi questo post sul blog per estrarre numeri da stringhe in SQL Server. Di seguito è riportato un esempio utilizzando una stringa nell’esempio:

 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) 

Puoi usare roba e patindex .

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

SQL Fiddle

Nel caso in cui ci siano alcuni caratteri possibili tra cifre (ad es. Migliaia di separatori), puoi provare a seguire:

 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 

L'output è:

 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 

Per l'aggiornamento, aggiungi ColToUpdate per selezionare l'elenco dei data cte:

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

Questo funziona bene per me:

 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 

Quindi chiama la funzione in modo tale per vedere l’originale qualcosa vicino al qualcosa di igienizzato:

 SELECT Something, dbo.StripNonNumerics(Something) FROM TableA 

Ecco una soluzione elegante se il tuo server supporta la funzione TRANSLATE (sul server sql è disponibile su sql server 2017+ e anche su sql azure).

Innanzitutto, sostituisce qualsiasi carattere non numerico con un carattere @. Quindi rimuove tutti i caratteri @. Potrebbe essere necessario aggiungere altri caratteri che potrebbero essere presenti nel secondo parametro della chiamata TRANSLATE.

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

Per aggiungere la risposta di Ken , questo gestisce le virgole, gli spazi e le parentesi

 --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 [email protected] Declare @r varchar(max) ='', @len int ,@c char(1), @x int = 0 Select @len = len(@originalWord) declare @pn varchar(100)[email protected] 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 @[email protected]+1 END 

Ecco una versione che estrae tutte le cifre da una stringa; cioè dato che 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. questo restituirebbe 35198224 . vale a dire dove hai dati numerici che potrebbero essere stati formattati come un codice (es. #123,456,789 / 123-00005 ), ma non è appropriato se stai cercando di estrarre numeri specifici (cioè al contrario di cifre / solo i caratteri numerici) dal testo. Inoltre gestisce solo cifre; quindi non restituirà segni negativi ( - ) o periodi . ).

 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 

Questo codice funziona rimuovendo tutte le cifre (cioè i caratteri che vogliamo) da una stringa data sostituendole con spazi vuoti. Quindi passa attraverso la stringa originale (che include le cifre) rimuovendo tutti i caratteri rimasti (cioè i caratteri non numerici), lasciando quindi solo le cifre.

Il motivo per cui lo facciamo in 2 passaggi, piuttosto che rimuovere tutti i caratteri non numerici in primo luogo è che ci sono solo 10 cifre, mentre c’è un numero enorme di possibili caratteri; quindi la sostituzione di quella piccola lista è relativamente veloce; quindi ci fornisce un elenco di quei caratteri non numerici che esistono effettivamente nella stringa, quindi possiamo sostituire quel piccolo set.

Il metodo utilizza SQL ricorsivo, utilizzando le espressioni di tabella comuni (CTE).

Ho creato una funzione per questo

 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 

Ecco la risposta:

 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 

Questo ha funzionato per me:

  1. Ho rimosso le virgolette singole.

  2. Ho quindi usato una sostituzione "," con "." .

Sicuramente questo aiuterà qualcuno:

 " & 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