Comment supprimer tous les caractères non-alphabétiques de string dans SQL Server?

Comment Pouvez-vous supprimer tous les caractères qui ne sont pas alphabétiques d'une chaîne de caractères?

et le non-alphanumérique?

est-ce que cela doit être une fonction personnalisée ou y a-t-il aussi des solutions plus généralisables?

145
demandé sur Even Mien 2009-06-17 19:16:30

19 réponses

essayez cette fonction:

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    Declare @KeepValues as varchar(50)
    Set @KeepValues = '%[^a-z]%'
    While PatIndex(@KeepValues, @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    Return @Temp
End

appelez ça comme ça:

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

une fois que vous comprenez le code, vous devriez voir qu'il est relativement simple de le changer pour supprimer les autres caractères, aussi. Vous pourriez même rendre cette dynamique assez pour passer dans votre modèle de recherche.

J'espère que ça aidera.

315
répondu G Mastros 2014-01-16 21:02:52

Paramétrée version de G Mastros ' réponse impressionnante :

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['+@MatchExpression+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

alphabétique seulement:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z')

numérique seulement:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^0-9')

alphanumérique seulement:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', '^a-z0-9')

Non alphanumérique:

SELECT dbo.fn_StripCharacters('a1!s2@d3#f4$', 'a-z0-9')
135
répondu Even Mien 2017-05-23 12:02:49

je savais que SQL était mauvais en manipulation de cordes, mais je ne pensais pas que ce serait aussi difficile. Voici une fonction simple pour enlever tous les nombres d'une chaîne. Il y aurait de meilleures façons de le faire, mais c'est un début.

CREATE FUNCTION dbo.AlphaOnly (
    @String varchar(100)
)
RETURNS varchar(100)
AS BEGIN
  RETURN (
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                REPLACE(
                  REPLACE(
                    REPLACE(
                      REPLACE(
                        @String,
                      '9', ''),
                    '8', ''),
                  '7', ''),
                '6', ''),
              '5', ''),
            '4', ''),
          '3', ''),
        '2', ''),
      '1', ''),
    '0', '')
  )
END
GO

-- ==================
DECLARE @t TABLE (
    ColID       int,
    ColString   varchar(50)
)

INSERT INTO @t VALUES (1, 'abc1234567890')

SELECT ColID, ColString, dbo.AlphaOnly(ColString)
FROM @t

Sortie

ColID ColString
----- ------------- ---
    1 abc1234567890 abc

Round 2 - Piloté Par Les Données De La Liste Noire

-- ============================================
-- Create a table of blacklist characters
-- ============================================
IF EXISTS (SELECT * FROM sys.tables WHERE [object_id] = OBJECT_ID('dbo.CharacterBlacklist'))
  DROP TABLE dbo.CharacterBlacklist
GO
CREATE TABLE dbo.CharacterBlacklist (
    CharID              int         IDENTITY,
    DisallowedCharacter nchar(1)    NOT NULL
)
GO
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'0')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'1')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'2')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'3')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'4')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'5')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'6')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'7')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'8')
INSERT INTO dbo.CharacterBlacklist (DisallowedCharacter) VALUES (N'9')
GO

-- ====================================
IF EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID('dbo.StripBlacklistCharacters'))
  DROP FUNCTION dbo.StripBlacklistCharacters
GO
CREATE FUNCTION dbo.StripBlacklistCharacters (
    @String nvarchar(100)
)
RETURNS varchar(100)
AS BEGIN
  DECLARE @blacklistCt  int
  DECLARE @ct           int
  DECLARE @c            nchar(1)

  SELECT @blacklistCt = COUNT(*) FROM dbo.CharacterBlacklist

  SET @ct = 0
  WHILE @ct < @blacklistCt BEGIN
    SET @ct = @ct + 1

    SELECT @String = REPLACE(@String, DisallowedCharacter, N'')
    FROM dbo.CharacterBlacklist
    WHERE CharID = @ct
  END

  RETURN (@String)
END
GO

-- ====================================
DECLARE @s  nvarchar(24)
SET @s = N'abc1234def5678ghi90jkl'

SELECT
    @s                  AS OriginalString,
    dbo.StripBlacklistCharacters(@s)   AS ResultString

Sortie

OriginalString           ResultString
------------------------ ------------
abc1234def5678ghi90jkl   abcdefghijkl

mon défi aux lecteurs: Pouvez-vous rendre cela plus efficace? Que penser de l'utilisation de la récursivité?

4
répondu Rob Garrison 2009-06-17 17:16:32

si vous êtes comme moi et que vous n'avez pas accès à simplement ajouter des fonctions à vos données de production mais que vous voulez tout de même effectuer ce genre de filtrage, voici une solution SQL pure en utilisant une table pivotante pour remettre les pièces filtrées ensemble.

N. B. j'ai codé en dur la table jusqu'à 40 caractères, vous aurez à ajouter de plus si vous avez plus de chaînes de filtre.

SET CONCAT_NULL_YIELDS_NULL OFF;

with 
    ToBeScrubbed
as (
    select 1 as id, '*SOME 222@ !@* #* BOGUS !@*&! DATA' as ColumnToScrub
),

Scrubbed as (
    select 
        P.Number as ValueOrder,
        isnull ( substring ( t.ColumnToScrub , number , 1 ) , '' ) as ScrubbedValue,
        t.id
    from
        ToBeScrubbed t
        left join master..spt_values P
            on P.number between 1 and len(t.ColumnToScrub)
            and type ='P'
    where
        PatIndex('%[^a-z]%', substring(t.ColumnToScrub,P.number,1) ) = 0
)

SELECT
    id, 
    [1]+ [2]+ [3]+ [4]+ [5]+ [6]+ [7]+ [8] +[9] +[10]
    +  [11]+ [12]+ [13]+ [14]+ [15]+ [16]+ [17]+ [18] +[19] +[20]
    +  [21]+ [22]+ [23]+ [24]+ [25]+ [26]+ [27]+ [28] +[29] +[30]
    +  [31]+ [32]+ [33]+ [34]+ [35]+ [36]+ [37]+ [38] +[39] +[40] as ScrubbedData
FROM (
    select 
        *
    from 
        Scrubbed
    ) 
    src
    PIVOT (
        MAX(ScrubbedValue) FOR ValueOrder IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
        [21], [22], [23], [24], [25], [26], [27], [28], [29], [30],
        [31], [32], [33], [34], [35], [36], [37], [38], [39], [40]
        )
    ) pvt
4
répondu Kyle Hale 2014-03-13 16:44:37

après avoir examiné toutes les solutions données, j'ai pensé qu'il devait y avoir une méthode SQL pure qui ne nécessite pas une fonction ou une requête CTE / XML, et ne nécessite pas difficile de maintenir emboîtés remplacer les énoncés. Voici ma solution:

SELECT 
  x
  ,CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 1, 1) + '%' THEN '' ELSE SUBSTRING(x, 1, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 2, 1) + '%' THEN '' ELSE SUBSTRING(x, 2, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 3, 1) + '%' THEN '' ELSE SUBSTRING(x, 3, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 4, 1) + '%' THEN '' ELSE SUBSTRING(x, 4, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 5, 1) + '%' THEN '' ELSE SUBSTRING(x, 5, 1) END
    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, 6, 1) + '%' THEN '' ELSE SUBSTRING(x, 6, 1) END
-- Keep adding rows until you reach the column size 
    AS stripped_column
FROM (SELECT 
        column_to_strip AS x
        ,'ABCDEFGHIJKLMNOPQRSTUVWXYZ' AS a 
      FROM my_table) a

l'avantage de le faire de cette façon est que les caractères valides sont contenus dans une chaîne de caractères dans la requête secondaire, ce qui rend facile à reconfigurer pour un ensemble différent de caractères.

le inconvénient est que vous devez ajouter une ligne de SQL pour chaque personnage jusqu'à la taille de votre colonne. Pour faciliter cette tâche j'ai juste utilisé le script Powershell ci-dessous, cet exemple si pour un VARCHAR (64):

1..64 | % {
  "    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe
3
répondu Dave Sexton 2015-07-22 13:51:47

croyez-le ou non, dans mon système Cette fonction laide exécute mieux que G Mastros élégant.

CREATE FUNCTION dbo.RemoveSpecialChar (@s VARCHAR(256)) 
RETURNS VARCHAR(256) 
WITH SCHEMABINDING
    BEGIN
        IF @s IS NULL
            RETURN NULL
        DECLARE @s2 VARCHAR(256) = '',
                @l INT = LEN(@s),
                @p INT = 1

        WHILE @p <= @l
            BEGIN
                DECLARE @c INT
                SET @c = ASCII(SUBSTRING(@s, @p, 1))
                IF @c BETWEEN 48 AND 57
                   OR  @c BETWEEN 65 AND 90
                   OR  @c BETWEEN 97 AND 122
                    SET @s2 = @s2 + CHAR(@c)
                SET @p = @p + 1
            END

        IF LEN(@s2) = 0
            RETURN NULL

        RETURN @s2
3
répondu J Brune 2016-12-20 16:04:53

C'est une façon très claire de prendre tous les personnages que vous ne voulez pas sortir. Problème est que vous devez spécifier les caractères que vous ne voulez pas. Si un nouveau personnage entre en vous, il passera à moins que vous ne l'ajoutiez à la liste.

L'avantage est que vous n'avez pas à créer une fonction spéciale. Je n'ai pas d'autorisations d'écriture, donc ça me permet d'exécuter à partir d'une simple requête.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
p.Name
,'®','')
,'©','')
,'ö','o')
,'ë','e')
,'ä','a')
,'ü','u')
,'ú','u')
,'í','i')
,'ï','i')
,'™','')
,'é','e')
,'²','2')
,'è','e')
,'—','-')
,'–','-')
,'ó','o')
,'•',' ')
,'…','.')
,'ô','o')
,'â','a')
,'á','a')
,'ê','e')
,'è','e')
,'’',' ')
,'·',' ')
,'à','a')
,'å','a')
,'ã','a')
,'’',' ')
,'a€s','as')
,'ø','o')
,'ñ','n')
,'î','i')
,'ç','c')
,'Ç','C')
,'Ã','A')
,'”','"')
,'“','"')
,'Á','A')
,'¢','c')
,'Ã','A')
,'Å','A')
,'¶','S')
,'×','x')
,'†','')
,'š','')
,'¤','')
,'µ','')
,'õ','')
,'€','')
,'‘','')
,'Õ','')
,'ð','')
,'Ò','')
,'¨','')
,'º','')
,'°','')
,'ì','')
,'ƒ','')
,'ÿ','')
,'ß','')
,'«','')
,'»','')
,'Æ','')
,'¬','')
,'Ù','')
,'ý','')
,'û','')
,'|','')
    as Name
2
répondu billmanH 2014-05-12 20:48:07

cette solution, inspirée de la solution de M. Allen, nécessite une table d'entiers Numbers (que vous devriez avoir sous la main si vous voulez effectuer des opérations de requête sérieuses avec de bonnes performances). Il ne nécessite pas une expression de table commune. Vous pouvez modifier l'expression NOT IN (...) pour exclure des caractères spécifiques, ou la changer en une expression IN (...) ou LIKE pour ne conserver que certains caractères.

SELECT (
    SELECT  SUBSTRING([YourString], N, 1)
    FROM    dbo.Numbers
    WHERE   N > 0 AND N <= CONVERT(INT, LEN([YourString]))
        AND SUBSTRING([YourString], N, 1) NOT IN ('(',')',',','.')
    FOR XML PATH('')
) AS [YourStringTransformed]
FROM ...
2
répondu Jay Bienvenu 2015-01-19 03:12:50

Voici une solution qui ne nécessite pas de créer une fonction ou de lister toutes les instances de caractères à remplacer. Il utilise une instruction récursive avec en combinaison avec un PATINDEX pour trouver des caractères indésirables. Il remplacera toutes les barres indésirables dans une colonne - jusqu'à 100 mauvais caractères uniques contenus dans une chaîne donnée. (Par exemple "ABC123DEF234" contiendrait 4 mauvais caractères 1, 2, 3 et 4) la limite de 100 est le nombre maximum de récursions autorisé dans une déclaration avec, mais cela n'impose pas un limite sur le nombre de lignes à traiter, ce qui n'est limitée que par la mémoire disponible.

Si vous ne voulez pas de résultats distincts, vous pouvez supprimer les deux options du code.

-- Create some test data:
SELECT * INTO #testData 
FROM (VALUES ('ABC DEF,K.l(p)'),('123H,J,234'),('ABCD EFG')) as t(TXT)

-- Actual query:
-- Remove non-alpha chars: '%[^A-Z]%'
-- Remove non-alphanumeric chars: '%[^A-Z0-9]%'
DECLARE @BadCharacterPattern VARCHAR(250) = '%[^A-Z]%';

WITH recurMain as (
    SELECT DISTINCT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
    FROM #testData
    UNION ALL
    SELECT CAST(TXT AS VARCHAR(250)) AS TXT, PATINDEX(@BadCharacterPattern, TXT) AS BadCharIndex
    FROM (
        SELECT 
            CASE WHEN BadCharIndex > 0 
                THEN REPLACE(TXT, SUBSTRING(TXT, BadCharIndex, 1), '')
                ELSE TXT 
            END AS TXT
        FROM recurMain
        WHERE BadCharIndex > 0
    ) badCharFinder
)
SELECT DISTINCT TXT
FROM recurMain
WHERE BadCharIndex = 0;
2
répondu Graeme Job 2015-08-18 10:47:45

Voici une autre façon de supprimer les caractères non-alphabétiques en utilisant un iTVF . Tout d'abord, vous avez besoin d'un répartiteur de chaîne basé sur le motif. Voici un extrait du camp de Dwain article :

-- PatternSplitCM will split a string based on a pattern of the form 
-- supported by LIKE and PATINDEX 
-- 
-- Created by: Chris Morris 12-Oct-2012 
CREATE FUNCTION [dbo].[PatternSplitCM]
(
       @List                VARCHAR(8000) = NULL
       ,@Pattern            VARCHAR(50)
) RETURNS TABLE WITH SCHEMABINDING 
AS 

RETURN
    WITH numbers AS (
        SELECT TOP(ISNULL(DATALENGTH(@List), 0))
            n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
        FROM
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
        (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
    )

    SELECT
        ItemNumber = ROW_NUMBER() OVER(ORDER BY MIN(n)),
        Item = SUBSTRING(@List,MIN(n),1+MAX(n)-MIN(n)),
        [Matched]
    FROM (
        SELECT n, y.[Matched], Grouper = n - ROW_NUMBER() OVER(ORDER BY y.[Matched],n)
        FROM numbers
        CROSS APPLY (
            SELECT [Matched] = CASE WHEN SUBSTRING(@List,n,1) LIKE @Pattern THEN 1 ELSE 0 END
        ) y
    ) d
    GROUP BY [Matched], Grouper

maintenant que vous avez un séparateur basé sur le motif, vous devez séparer les chaînes qui correspondent au motif:

[a-z]

et ensuite les concaténer de nouveau pour obtenir le résultat désiré:

SELECT *
FROM tbl t
CROSS APPLY(
    SELECT Item + ''
    FROM dbo.PatternSplitCM(t.str, '[a-z]')
    WHERE Matched = 1
    ORDER BY ItemNumber
    FOR XML PATH('')
) x (a)

échantillon

résultat:

| Id |              str |              a |
|----|------------------|----------------|
|  1 |    test“te d'abc |     testtedabc |
|  2 |            anr¤a |           anra |
|  3 |  gs-re-C“te d'ab |     gsreCtedab |
|  4 |         M‚fe, DF |          MfeDF |
|  5 |           R™temd |          Rtemd |
|  6 |          ™jad”ji |          jadji |
|  7 |      Cje y ret¢n |       Cjeyretn |
|  8 |        J™kl™balu |        Jklbalu |
|  9 |       le“ne-iokd |       leneiokd |
| 10 |   liode-Pyr‚n‚ie |    liodePyrnie |
| 11 |         V„s G”ta |          VsGta |
| 12 |        Sƒo Paulo |        SoPaulo |
| 13 |  vAstra gAtaland | vAstragAtaland |
| 14 |  ¥uble / Bio-Bio |     ubleBioBio |
| 15 | U“pl™n/ds VAsb-y |    UplndsVAsby |
2
répondu Felix Pamittan 2016-03-18 06:41:00

j'ai mis ceci dans les deux endroits où PatIndex est appelé.

PatIndex('%[^A-Za-z0-9]%', @Temp)

pour la fonction personnalisée ci-dessus RemoveNonAlphaCharacters et renommé RemoveNonAlphaNumericCharacters

1
répondu user381460 2011-12-18 14:37:16

--première création d'une fonction

CREATE FUNCTION [dbo].[GetNumericonly]
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
     DECLARE @intAlpha INT
     SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
     WHILE @intAlpha > 0
   BEGIN
          SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )
          SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
   END
END
RETURN ISNULL(@strAlphaNumeric,0)
END

appelez maintenant cette fonction comme

select [dbo].[GetNumericonly]('Abhi12shek23jaiswal')

son résultat comme

1223
1
répondu Abhishek Jaiswal 2016-07-19 08:15:51

du point de vue de la performance, j'utiliserais la fonction Inline:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_RemoveNumericCharsFromString]
(
@List NVARCHAR(4000)
)
RETURNS TABLE 
AS RETURN

    WITH GetNums AS (
       SELECT TOP(ISNULL(DATALENGTH(@List), 0))
        n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
        FROM
          (VALUES (0),(0),(0),(0)) d (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) e (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) f (n),
          (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) g (n)
            )

    SELECT StrOut = ''+
        (SELECT Chr
         FROM GetNums
            CROSS APPLY (SELECT SUBSTRING(@List , n,1)) X(Chr)
         WHERE Chr LIKE '%[^0-9]%' 
         ORDER BY N
         FOR XML PATH (''),TYPE).value('.','NVARCHAR(MAX)')


   /*How to Use
   SELECT StrOut FROM dbo.udf_RemoveNumericCharsFromString ('vv45--9gut')
   Result: vv--gut
   */
1
répondu hkravitz 2016-12-27 15:38:56

utilisant une table de nombres générés par CTE pour examiner chaque caractère, puis pour XML de concatter à une chaîne de valeurs conservées que vous pouvez...

CREATE FUNCTION [dbo].[PatRemove](
    @pattern varchar(50),
    @expression varchar(8000) 
    )
RETURNS varchar(8000)
AS
BEGIN
    WITH 
        d(d) AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) digits(d)),
        nums(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM d d1, d d2, d d3, d d4),
        chars(c) AS (SELECT SUBSTRING(@expression, n, 1) FROM nums WHERE n <= LEN(@expression))
    SELECT 
        @expression = (SELECT c AS [text()] FROM chars WHERE c NOT LIKE @pattern FOR XML PATH(''));

    RETURN @expression;
END
0
répondu Dennis Allen 2014-08-06 16:27:51
DECLARE @vchVAlue NVARCHAR(255) = 'SWP, Lettering Position 1: 4 Ω, 2: 8 Ω, 3: 16 Ω, 4:  , 5:  , 6:  , Voltage Selector, Solder, 6, Step switch, : w/o fuseholder '


WHILE PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))) > 0
  BEGIN
    SELECT @vchVAlue = STUFF(@vchVAlue,PATINDEX('%?%' , CAST(@vchVAlue AS VARCHAR(255))),1,' ')
  END 

SELECT @vchVAlue
0
répondu Mohannad Qattash 2016-09-27 09:41:14

cette façon n'a pas fonctionné pour moi car j'ai essayé de garder les lettres arabes que j'ai essayé de remplacer l'expression régulière mais aussi il n'a pas fonctionné. j'ai écrit une autre méthode pour travailler au niveau ASCII car c'était mon seul choix et ça a fonctionné.

 Create function [dbo].[RemoveNonAlphaCharacters] (@s varchar(4000)) returns varchar(4000)
   with schemabinding
begin
   if @s is null
      return null
   declare @s2 varchar(4000)
   set @s2 = ''
   declare @l int
   set @l = len(@s)
   declare @p int
   set @p = 1
   while @p <= @l begin
      declare @c int
      set @c = ascii(substring(@s, @p, 1))
      if @c between 48 and 57 or @c between 65 and 90 or @c between 97 and 122 or @c between 165 and 253 or @c between 32 and 33
         set @s2 = @s2 + char(@c)
      set @p = @p + 1
      end
   if len(@s2) = 0
      return null
   return @s2
   end

GO

0
répondu AGOMAA 2016-10-26 11:32:18

Voici une autre solution CTE récursive, basée sur la réponse DE @Gerhard Weiss ici . Vous devriez pouvoir copier et coller tout le bloc de code dans SSMS et jouer avec. Les résultats comprennent quelques colonnes supplémentaires pour nous aider à comprendre ce qui se passe. Il m'a fallu un certain temps avant de comprendre tout ce qui se passait avec PATINDEX (RegEx) et le CTE récursif.

DECLARE @DefineBadCharPattern varchar(30)
SET @DefineBadCharPattern = '%[^A-z]%'  --Means anything NOT between A and z characters (according to ascii char value) is "bad"
SET @DefineBadCharPattern = '%[^a-z0-9]%'  --Means anything NOT between a and z characters or numbers 0 through 9 (according to ascii char value) are "bad"
SET @DefineBadCharPattern = '%[^ -~]%'  --Means anything NOT between space and ~ characters (all non-printable characters) is "bad"
--Change @ReplaceBadCharWith to '' to strip "bad" characters from string
--Change to some character if you want to 'see' what's being replaced. NOTE: It must be allowed accoring to @DefineBadCharPattern above
DECLARE @ReplaceBadCharWith varchar(1) = '#'  --Change this to whatever you want to replace non-printable chars with 
IF patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, @ReplaceBadCharWith) > 0
    BEGIN
        RAISERROR('@ReplaceBadCharWith value (%s) must be a character allowed by PATINDEX pattern of %s',16,1,@ReplaceBadCharWith, @DefineBadCharPattern)
        RETURN
    END
--A table of values to play with:
DECLARE @temp TABLE (OriginalString varchar(100))
INSERT @temp SELECT ' 1hello' + char(13) + char(10) + 'there' + char(30) + char(9) + char(13) + char(10)
INSERT @temp SELECT '2hello' + char(30) + 'there' + char(30)
INSERT @temp SELECT ' 3hello there'
INSERT @temp SELECT ' tab' + char(9) + ' character'
INSERT @temp SELECT 'good bye'

--Let the magic begin:
;WITH recurse AS (
    select
    OriginalString,
    OriginalString as CleanString,
    patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString) as [Position],
    substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1) as [InvalidCharacter],
    ascii(substring(OriginalString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN, OriginalString),1)) as [ASCIICode]
    from @temp
   UNION ALL
    select
    OriginalString,
    CONVERT(varchar(100),REPLACE(CleanString,InvalidCharacter,@ReplaceBadCharWith)),
    patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) as [Position],
    substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1),
    ascii(substring(CleanString,patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString),1))
    from recurse
    where patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) > 0
)
SELECT * FROM recurse
--optionally comment out this last WHERE clause to see more of what the recursion is doing:
WHERE patindex(@DefineBadCharPattern COLLATE Latin1_General_BIN,CleanString) = 0
0
répondu Baodad 2018-06-01 21:48:49

bien que post soit un peu vieux, je voudrais dire ce qui suit. Le problème que j'ai eu avec la solution ci-dessus est qu'il ne filtre pas les caractères comme ç, ë, ï, etc. J'ai adapté une fonction comme suit (j'ai seulement utilisé une chaîne de 80 varchars pour sauver la mémoire):

create FUNCTION dbo.udf_Cleanchars (@InputString varchar(80)) 
RETURNS varchar(80) 
AS 

BEGIN 
declare @return varchar(80) , @length int , @counter int , @cur_char char(1) 
SET @return = '' 
SET @length = 0 
SET @counter = 1 
SET @length = LEN(@InputString) 
IF @length > 0 
BEGIN WHILE @counter <= @length 

BEGIN SET @cur_char = SUBSTRING(@InputString, @counter, 1) IF ((ascii(@cur_char) in (32,44,46)) or (ascii(@cur_char) between 48 and 57) or (ascii(@cur_char) between 65 and 90) or (ascii(@cur_char) between 97 and 122))
BEGIN SET @return = @return + @cur_char END 
SET @counter = @counter + 1 
END END 

RETURN @return END
-1
répondu Eric 2016-08-30 13:16:11

je viens de trouver ceci intégré dans Oracle 10g si c'est ce que vous utilisez. J'ai dû enlever tous les caractères spéciaux pour comparer les numéros de téléphone.

regexp_replace(c.phone, '[^0-9]', '')
-3
répondu Lu_Bu 2014-08-01 15:54:40