T-SQL select query pour supprimer les caractères non numériques

J’ai des données sales dans une colonne avec une longueur alpha variable. Je veux juste supprimer tout ce qui n’est pas 0-9.

Je ne veux pas exécuter une fonction ou un proc. J’ai un script similaire qui saisit juste la valeur numérique après le texte, il ressemble à ceci:

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

Je pensais que cela fonctionnerait très bien jusqu’à ce que je trouve que certains des champs de données que je pensais être au format Alpha # 12345789 ne sont pas …

Exemples de données à supprimer

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

Je veux juste le 123. Il est vrai que tous les champs de données ont le # avant le nombre.

J’ai essayé la souschaîne et PatIndex, mais je ne comprends pas bien la syntaxe ou quelque chose. Quelqu’un at-il des conseils sur la meilleure façon de résoudre ce problème?

Je vous remercie!

Voir cet article de blog sur l’extraction des numéros de chaînes dans SQL Server. Vous trouverez ci-dessous un exemple utilisant une chaîne dans votre exemple:

 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) 

Vous pouvez utiliser des choses et patindex .

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

Violon SQL

Si certains caractères sont possibles entre des chiffres (par exemple, des séparateurs de milliers), vous pouvez essayer les suivants:

 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, subssortingng(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 

Le résultat est:

 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 

Pour la mise à jour, ajoutez ColToUpdate pour sélectionner la liste des data :

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

Cela fonctionne bien pour moi:

 CREATE FUNCTION [dbo].[SsortingpNonNumerics] ( @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 

Puis appelez la fonction comme pour voir l’original à côté de quelque chose:

 SELECT Something, dbo.SsortingpNonNumerics(Something) FROM TableA 

Voici une solution élégante si votre serveur prend en charge la fonction TRANSLATE (sur le serveur sql, il est disponible sur sql server 2017+ et aussi sql azuree).

Tout d’abord, il remplace tous les caractères non numériques par un caractère @. Ensuite, il supprime tous les caractères @. Vous devrez peut-être append des caractères supplémentaires susceptibles de figurer dans le deuxième paramètre de l’appel TRANSLATE.

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

Pour append à la réponse de Ken , cela traite les virgules et les espaces et les parenthèses

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

Voici une version qui extrait tous les chiffres d’une chaîne. c’est-à-dire que 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. cela retournerait 35198224 . c’est-à-dire que vous avez des données numériques formatées sous forme de code (par exemple, #123,456,789 / 123-00005 ), mais que vous ne souhaitez pas utiliser pour des nombres spécifiques (par exemple, contrairement aux chiffres / juste les caractères numériques) du texte. En outre, il ne traite que les chiffres; ne renverra donc pas de signes négatifs ( - ) ou de périodes . ).

 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 ssortingng 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 ssortingng, putting the result into the numerics column , Numerics as ( select id , replace(original, subssortingng(nonNumeric,1,1), '') numerics , replace(nonNumeric, subssortingng(nonNumeric,1,1), '') charsToreplace , len(replace(nonNumeric, subssortingng(nonNumeric,1,1), '')) charsRemaining from NonNumerics union all select id , replace(numerics, subssortingng(charsToreplace,1,1), '') numerics , replace(charsToreplace, subssortingng(charsToreplace,1,1), '') charsToreplace , len(replace(charsToreplace, subssortingng(charsToreplace,1,1), '')) charsRemaining from Numerics where charsRemaining > 0 ) --we select only those ssortingngs 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 

Ce code fonctionne en supprimant tous les chiffres (c.-à-d. Les caractères que nous voulons) d’une chaîne donnée en les remplaçant par des espaces. Ensuite, il passe par la chaîne originale (qui comprend les chiffres) en supprimant tous les caractères restants (c.-à-d. Les caractères non numériques), ne laissant que les chiffres.

La raison pour laquelle nous faisons cela en 2 étapes, plutôt que de supprimer tous les caractères non numériques en premier lieu, est qu’il n’y a que 10 chiffres, alors qu’il y a un grand nombre de caractères possibles. remplacer cette petite liste est donc relativement rapide; nous donne alors une liste des caractères non numériques qui existent dans la chaîne, nous pouvons donc remplacer ce petit ensemble.

La méthode utilise le SQL récursif, en utilisant des expressions de table communes (CTE).

J’ai créé une fonction pour cela

 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 

Voici la réponse:

 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 

Cela a fonctionné pour moi:

  1. J’ai supprimé les guillemets simples.

  2. J’ai ensuite utilisé un "," avec "." .

Cela aidera sûrement quelqu’un:

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