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

Dans votre cas, il semble que le # se trouvera toujours après le symbole #. L'utilisation de CHARINDEX() avec LTRIM() et RTRIM() serait probablement la meilleure solution. Mais voici une méthode intéressante pour se débarrasser de N'IMPORTE QUEL non chiffre. Elle utilise une table de pointage et une table de chiffres pour limiter les caractères acceptés, puis une technique XML pour concaténer en une seule chaîne sans les caractères non numériques. L'intérêt de cette technique est qu'elle pourrait être étendue pour inclure N'IMPORTE QUEL caractère autorisé et éliminer tout ce qui ne l'est pas.

DECLARE @ExampleData AS TABLE (Col VARCHAR(100))
INSERT INTO @ExampleData (Col) VALUES ('AB ABCDE # 123'),('ABCDE# 123'),('AB: ABC# 123')

DECLARE @Digits AS TABLE (D CHAR(1))
INSERT INTO @Digits (D) VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),('8'),('9')

;WITH cteTally AS (
SELECT
    I = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM
    @Digits d10
    CROSS APPLY @Digits d100
    --add more cross applies to cover longer fields this handles 100
)

SELECT *
FROM
    @ExampleData e
    OUTER APPLY (
    SELECT CleansedPhone = CAST((
    SELECT TOP 100
       SUBSTRING(e.Col,t.I,1)
    FROM
       cteTally t
       INNER JOIN @Digits d
       ON SUBSTRING(e.Col,t.I,1) = d.D
    WHERE
       I <= LEN(e.Col)
    ORDER BY
       t.I
    FOR XML PATH('')) AS VARCHAR(100))) o

Answer 2

 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

Answer 3

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('%[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

Answer 4

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('%[a-z]%', tVal) = 0
       ) t
       INNER JOIN @t t1
            ON  t.original = t1.tVal
WHERE  t.sl = 1

Answer 5

Vous pouvez créer une fonction scalaire SQL CLR afin de pouvoir utiliser des expressions régulières comme des motifs de remplacement.

Ici vous trouverez un exemple de création d'une telle fonction.

Une telle fonction résoudra le problème avec les lignes suivantes :

SELECT [dbo].[fn_Utils_RegexReplace] ('AB ABCDE # 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('ABCDE# 123', '[^0-9]', '');
SELECT [dbo].[fn_Utils_RegexReplace] ('AB: ABC# 123', '[^0-9]', '');

Plus important encore, vous serez en mesure de résoudre des problèmes plus complexes car les expressions régulières apporteront un tout nouveau monde d'options directement dans vos déclarations T-SQL.