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

Je suis arrivé assez tard à la fête, mais j'ai trouvé ce qui suit qui m'a semblé très efficace si quelqu'un cherche encore

SELECT
    (SELECT CAST(CAST((
        SELECT SUBSTRING(FieldToStrip, Number, 1)
        FROM master..spt_values
        WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
            SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
    AS xml) AS varchar(MAX)))
FROM
    SourceTable

Answer 2

CREATE FUNCTION FN_RemoveNonNumeric (@Input NVARCHAR(512))
RETURNS NVARCHAR(512)
AS
BEGIN
DECLARE @Trimmed NVARCHAR(512)

SELECT @Trimmed = @Input

WHILE PATINDEX('%[^0-9]%', @Trimmed) > 0
    SELECT @Trimmed = REPLACE(@Trimmed, SUBSTRING(@Trimmed, PATINDEX('%[^0-9]%', @Trimmed), 1), '')

RETURN @Trimmed
END

GO

SELECT dbo.FN_RemoveNonNumeric('ABCDE# 123')

Answer 3

Voici une version qui extrait tous les chiffres d'une chaîne de caractères ; c'est-à-dire que, étant donné I'm 35 years old; I was born in 1982. The average family has 2.4 children. ce qui donnerait 35198224 . c'est-à-dire qu'il est bon lorsque vous avez des données numériques qui peuvent avoir été formatées sous forme de code (par exemple, le code de l'utilisateur). #123,456,789 / 123-00005 ), mais elle n'est pas appropriée si vous cherchez à extraire des nombres spécifiques (par opposition aux chiffres / uniquement les caractères numériques) du texte. De plus, elle ne traite que les chiffres, et ne renvoie donc pas les signes négatifs ( - ) ou des 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 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

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

La raison pour laquelle nous procédons en deux é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 très grand nombre de caractères possibles ; ainsi, remplacer cette petite liste est relativement rapide ; ensuite, cela nous donne une liste des caractères non numériques qui existent réellement dans la chaîne de caractères, de sorte que nous pouvons ensuite remplacer ce petit ensemble.

La méthode fait appel au SQL récursif, en utilisant des expressions de table communes (CTE).

Answer 4

Pour compléter Ken réponse, ceci gère les virgules, 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

Answer 5

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