Sélectionner la requête pour supprimer les caractères non numériques

Question

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

Je ne veux pas exécuter une fonction ou une procédure. J'ai un script qui est similaire qui ne fait que saisir 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 bien jusqu'à ce que je découvre que certains des champs de données que je pensais être au format Alpha # 12345789 ne le sont pas.

Exemples de données qui doivent être dépouillées

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

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

J'ai essayé substring et PatIndex, mais la syntaxe n'est pas tout à fait correcte. Quelqu'un a-t-il des conseils sur la meilleure façon de résoudre ce problème ?

Answer 1

Voir ceci article de blog sur l'extraction de nombres à partir de chaînes de caractères dans SQL Server. Vous trouverez ci-dessous un exemple utilisant une chaîne de caractères :

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)

Answer 2

Voici une solution élégante si votre serveur supporte la fonction TRANSLATE (sur sql server elle est disponible sur sql server 2017+ et aussi sql azure).

Tout d'abord, il remplace tout caractère non numérique par un caractère @. Ensuite, il supprime tous les caractères @. Vous pouvez avoir besoin d'ajouter des caractères supplémentaires que vous savez être présents dans le second paramètre de l'appel TRANSLATE.

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

Answer 3

Vous pouvez utiliser trucs y patindex .

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

Fiddle SQL

Answer 4

Cela fonctionne bien pour moi :

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

Ensuite, appelez la fonction comme ceci pour voir le quelque chose original à côté du quelque chose nettoyé :

SELECT Something, dbo.StripNonNumerics(Something) FROM TableA

Answer 5

S'il y a des caractères possibles entre les chiffres (par exemple, des séparateurs de milliers), vous pouvez essayer ce qui suit :

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 sortie 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 cte :

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