[sql-server] 영문자 이외의 문자를 SQL Server의 문자열에서 제거하는 방법은 무엇입니까?

Program / 글쓴이

문자열에서 알파벳이 아닌 모든 문자를 어떻게 제거 할 수 있습니까?

영숫자가 아닌 것은 어떻습니까?

이것이 사용자 정의 기능이어야합니까, 아니면 더 일반화 가능한 솔루션이 있습니까?

답변

이 기능을 사용해보십시오 :

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

다음과 같이 호출하십시오.

Select dbo.RemoveNonAlphaCharacters('abc1234def5678ghi90jkl')

코드를 이해하면 다른 문자도 제거하기 위해 코드를 변경하는 것이 비교적 간단하다는 것을 알 수 있습니다. 검색 패턴을 전달할 수있을 정도로 동적으로 만들 수도 있습니다.

도움이 되길 바랍니다.

답변

G Mastros 의 멋진 답변 의 매개 변수화 된 버전 :

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

알파벳 만 :

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

숫자 만 :

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

영숫자 만 :

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

영숫자가 아닌 :

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

답변

내 시스템 에서이 추악한 기능은 G Mastros 우아한 기능보다 성능이 뛰어납니다.

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

답변

SQL이 문자열 조작에 좋지 않다는 것을 알았지 만 이것이 어렵다고 생각하지 않았습니다. 다음은 문자열에서 모든 숫자를 제거하는 간단한 함수입니다. 이를 수행하는 더 좋은 방법이 있지만 이것은 시작입니다.

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

산출

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

라운드 2-데이터 중심 블랙리스트

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

산출

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

독자에 대한 나의 도전 : 이것을보다 효율적으로 만들 수 있습니까? 재귀를 사용하는 것은 어떻습니까?

답변

나와 같은데 프로덕션 데이터에 함수를 추가 할 수는 없지만 여전히 이런 종류의 필터링을 수행하려는 경우 PIVOT 테이블을 사용하여 필터링 된 조각을 다시 모으는 순수한 SQL 솔루션이 있습니다.

NB 표를 최대 40 자로 하드 코딩했습니다. 필터링 할 문자열이 더 길면 더 추가해야합니다.

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

답변

주어진 모든 솔루션을 살펴본 결과 함수 또는 CTE / XML 쿼리가 필요하지 않고 중첩 된 REPLACE 문을 유지 관리하는 데 어려움이없는 순수한 SQL 메서드가 있어야한다고 생각했습니다. 내 해결책은 다음과 같습니다.

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

이 방법을 사용하면 하위 쿼리의 한 문자열에 유효한 문자가 포함되어 다른 문자 집합을 쉽게 재구성 할 수 있다는 이점이 있습니다.

단점은 각 문자에 대해 열 크기까지 SQL 행을 추가해야한다는 것입니다. 이 작업을 쉽게하기 위해 아래의 Powershell 스크립트를 사용했습니다.

1..64 | % {
  "    + CASE WHEN a NOT LIKE '%' + SUBSTRING(x, {0}, 1) + '%' THEN '' ELSE SUBSTRING(x, {0}, 1) END" -f $_
} | clip.exe

답변

다음을 사용하여 알파벳이 아닌 문자를 제거하는 다른 방법이 iTVF있습니다. 먼저 패턴 기반 문자열 스플리터가 필요합니다. 다음은 Dwain Camp의 기사 에서 가져온 것입니다 .

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

이제 패턴 기반 스플리터가 있으므로 패턴과 일치하는 문자열을 분할해야합니다.

[a-z]

그런 다음 원하는 결과를 얻기 위해 다시 연결하십시오.

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)

견본

결과:

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