SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

programing

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

linuxpc 2023. 7. 15. 09:45

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

SQL Server를 사용하여 ASCII가 아닌 문자가 포함된 행을 반환하는 방법은 무엇입니까?
한 칸에 어떻게 하는지 보여주시면 감사하겠습니다.

나는 지금 이런 일을 하고 있지만, 작동하지 않습니다.

select *
from Staging.APARMRE1 as ar
where ar.Line like '%[^!-~ ]%'

추가 크레딧을 위해, 만약 그것이 모든 것을 포함할 수 있다면. varchar테이블의 열, 그것은 탁월할 것입니다!이 솔루션에서는 세 개의 열을 반환하는 것이 좋습니다.

해당 레코드의 ID 필드입니다. 이렇게 하면 전체 레코드를 다른 쿼리로 검토할 수 있습니다.
열 이름
잘못된 문자가 있는 텍스트

 Id | FieldName | InvalidText       |
----+-----------+-------------------+
 25 | LastName  | Solís             |
 56 | FirstName | François          |
100 | Address1  | 123 Ümlaut street |

잘못된 문자는 SPACE(32₁₀) ~ 범위를 벗어납니다.~(127₁₀)

다음은 PATINDEX를 사용한 단일 열 검색 솔루션입니다.
또한 StartPosition, InvalidCharacter 및 ASCII 코드도 표시됩니다.

select line,
  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,Line) as [Position],
  substring(line,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,Line),1) as [InvalidCharacter],
  ascii(substring(line,patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,Line),1)) as [ASCIICode]
from  staging.APARMRE1
where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,Line) >0

나는 이 코드를 성공적으로 실행해 왔습니다.

declare @UnicodeData table (
     data nvarchar(500)
)
insert into 
    @UnicodeData
values 
    (N'Horse�')
    ,(N'Dog')
    ,(N'Cat')

select
    data
from
    @UnicodeData 
where
    data collate LATIN1_GENERAL_BIN != cast(data as varchar(max))

알려진 열에 적합합니다.

추가 크레딧을 위해 주어진 테이블의 모든 nvarchar 열에서 유니코드 문자를 검색하기 위해 이 빠른 스크립트를 작성했습니다.

declare 
    @sql    varchar(max)    = ''
    ,@table sysname         = 'mytable' -- enter your table here

;with ColumnData as (
    select
        RowId               = row_number() over (order by c.COLUMN_NAME)
        ,c.COLUMN_NAME
        ,ColumnName         = '[' + c.COLUMN_NAME + ']'
        ,TableName          = '[' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']' 
    from
        INFORMATION_SCHEMA.COLUMNS c
    where
        c.DATA_TYPE         = 'nvarchar'
        and c.TABLE_NAME    = @table
)
select
    @sql = @sql + 'select FieldName = ''' + c.ColumnName + ''',         InvalidCharacter = [' + c.COLUMN_NAME + ']  from ' + c.TableName + ' where ' + c.ColumnName + ' collate LATIN1_GENERAL_BIN != cast(' + c.ColumnName + ' as varchar(max)) '  +  case when c.RowId <> (select max(RowId) from ColumnData) then  ' union all ' else '' end + char(13)
from
    ColumnData c

-- check
-- print @sql
exec (@sql)

저는 동적 SQL의 팬은 아니지만 이러한 탐색적 쿼리에 사용됩니다.

다음과 같은 방법을 시도:

DECLARE @YourTable table (PK int, col1 varchar(20), col2 varchar(20), col3 varchar(20));
INSERT @YourTable VALUES (1, 'ok','ok','ok');
INSERT @YourTable VALUES (2, 'BA'+char(182)+'D','ok','ok');
INSERT @YourTable VALUES (3, 'ok',char(182)+'BAD','ok');
INSERT @YourTable VALUES (4, 'ok','ok','B'+char(182)+'AD');
INSERT @YourTable VALUES (5, char(182)+'BAD','ok',char(182)+'BAD');
INSERT @YourTable VALUES (6, 'BAD'+char(182),'B'+char(182)+'AD','BAD'+char(182)+char(182)+char(182));

--if you have a Numbers table use that, other wise make one using a CTE
WITH AllNumbers AS
(   SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<1000
)
SELECT 
    pk, 'Col1' BadValueColumn, CONVERT(varchar(20),col1) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col1)
    WHERE ASCII(SUBSTRING(y.col1, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col1, n.Number, 1))>127
UNION
SELECT 
    pk, 'Col2' BadValueColumn, CONVERT(varchar(20),col2) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col2)
    WHERE ASCII(SUBSTRING(y.col2, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col2, n.Number, 1))>127
UNION
SELECT 
    pk, 'Col3' BadValueColumn, CONVERT(varchar(20),col3) AS BadValue --make the XYZ in convert(varchar(XYZ), ...) the largest value of col1, col2, col3
    FROM @YourTable           y
        INNER JOIN AllNumbers n ON n.Number <= LEN(y.col3)
    WHERE ASCII(SUBSTRING(y.col3, n.Number, 1))<32 OR ASCII(SUBSTRING(y.col3, n.Number, 1))>127
order by 1
OPTION (MAXRECURSION 1000);

출력:

pk          BadValueColumn BadValue
----------- -------------- --------------------
2           Col1           BA¶D
3           Col2           ¶BAD
4           Col3           B¶AD
5           Col1           ¶BAD
5           Col3           ¶BAD
6           Col1           BAD¶
6           Col2           B¶AD
6           Col3           BAD¶¶¶

(8 row(s) affected)

이 스크립트는 한 열에서 ASCII가 아닌 문자를 검색합니다.유효한 모든 문자의 문자열을 생성합니다. 여기서 코드 포인트는 32에서 127입니다.그런 다음 목록과 일치하지 않는 행을 검색합니다.

declare @str varchar(128);
declare @i int;
set @str = '';
set @i = 32;
while @i <= 127
    begin
    set @str = @str + '|' + char(@i);
    set @i = @i + 1;
    end;

select  col1
from    YourTable
where   col1 like '%[^' + @str + ']%' escape '|';

일부 실제 데이터에서 다양한 솔루션을 실행합니다. 즉, 1200만 행의 varchar 길이 ~30, 약 9k의 dodgy 행, 실행 중인 전체 텍스트 색인 없음, patIndex 솔루션이 가장 빠르며 가장 많은 행을 선택합니다.

(km. 사전 실행을 통해 캐시를 알려진 상태로 설정하고, 3개의 프로세스를 실행한 후 최종적으로 다시 km를 실행함 - 마지막 2회 주행 시 2초 이내에 시간이 주어짐)

Gerhard Weiss의 patindex 솔루션 - 런타임 0:38, 9144개 행을 반환합니다.

select dodgyColumn from myTable fcc
WHERE  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,dodgyColumn ) >0

MT의 하위 문자열 번호 솔루션 -- 런타임 1:16, 반환된 8996 행

select dodgyColumn from myTable fcc
INNER JOIN dbo.Numbers32k dn ON dn.number<(len(fcc.dodgyColumn ))
WHERE ASCII(SUBSTRING(fcc.dodgyColumn , dn.Number, 1))<32 
    OR ASCII(SUBSTRING(fcc.dodgyColumn , dn.Number, 1))>127

Deon Robertson의 udf 솔루션 -- 런타임 3:47, 7316개 행을 반환합니다.

select dodgyColumn 
from myTable 
where dbo.udf_test_ContainsNonASCIIChars(dodgyColumn , 1) = 1

사용자 정의 기능은 'Parse Alphanumeric' 웹에서 사용할 수 있습니다.Google UDF가 영숫자를 구문 분석하면 해당 코드를 찾을 수 있습니다.이 사용자 정의 함수는 0-9, a-z 및 A-Z 사이에 맞지 않는 모든 문자를 제거합니다.

Select * from Staging.APARMRE1 ar
where udf_parsealpha(ar.last_name) <> ar.last_name

그러면 성_이름에 잘못된 문자가 있는 레코드가 모두 반환됩니다.보너스 포인트 질문이 좀 더 어려운 질문이긴 하지만, 사례 설명을 통해 해결할 수 있을 것 같습니다.이것은 약간의 가짜 코드입니다. 그것이 작동할지 완전히 확신할 수는 없습니다.

Select id, case when udf_parsealpha(ar.last_name) <> ar.last_name then 'last name'
when udf_parsealpha(ar.first_name) <> ar.first_name then 'first name'
when udf_parsealpha(ar.Address1) <> ar.last_name then 'Address1'
end, 
case when udf_parsealpha(ar.last_name) <> ar.last_name then ar.last_name
when udf_parsealpha(ar.first_name) <> ar.first_name then ar.first_name
when udf_parsealpha(ar.Address1) <> ar.last_name then ar.Address1
end
from Staging.APARMRE1 ar
where udf_parsealpha(ar.last_name) <> ar.last_name or
udf_parsealpha(ar.first_name) <> ar.first_name or
udf_parsealpha(ar.Address1) <> ar.last_name

포럼 우편함에 이렇게 적었습니다...그래서 저는 그것이 그대로 작동할지 확신할 수 없지만, 가까운 곳에 있어야 합니다.하나의 레코드에 잘못된 문자가 포함된 두 개의 필드가 있는 경우 어떻게 동작할지 잘 모르겠습니다.

또는 from 절을 단일 테이블에서 다음과 같은 하위 쿼리로 변경할 수 있습니다.

select id,fieldname,value from (
Select id,'last_name' as 'fieldname', last_name as 'value'
from Staging.APARMRE1 ar
Union
Select id,'first_name' as 'fieldname', first_name as 'value'
from Staging.APARMRE1 ar
---(and repeat unions for each field)
)
where udf_parsealpha(value) <> value

여기서의 이점은 모든 열에 대해 여기서 조합 명세서를 확장하기만 하면 되는 반면, 이 스크립트의 사례 명세서 버전에 있는 모든 열에 대해 비교를 세 번 넣어야 합니다.

잘못된 문자가 포함된 필드 찾기

SELECT * FROM Staging.APARMRE1 FOR XML AUTO, TYPE

다음 쿼리를 사용하여 테스트할 수 있습니다.

SELECT top 1 'char 31: '+char(31)+' (hex 0x1F)' field
from sysobjects
FOR XML AUTO, TYPE

결과는 다음과 같습니다.

Msg 6841, Level 16, State 1, Line 3 FOR XML에 XML에서 허용되지 않는 문자(0x001F)가 포함되어 있기 때문에 노드 'field'에 대한 데이터를 직렬화할 수 없습니다.FOR XML을 사용하여 이 데이터를 검색하려면 이진, varbinary 또는 이미지 데이터 유형으로 변환하고 BINARY BASE64 지시어를 사용합니다.

xml 파일을 작성하고 유효성을 검사할 때 잘못된 문자 오류가 발생할 때 매우 유용합니다.

확장 ASCII 문자가 포함된 c열을 탐지하기 위해 작성된 UDFI입니다.빠르고 확인하고자 하는 문자 집합을 확장할 수 있습니다.두 번째 매개 변수를 사용하면 표준 문자 집합 외부의 항목을 선택하거나 확장 집합을 허용할 수 있습니다.

create function [dbo].[udf_ContainsNonASCIIChars]
(
@string nvarchar(4000),
@checkExtendedCharset bit
)
returns bit
as
begin

    declare @pos int = 0;
    declare @char varchar(1);
    declare @return bit = 0;

    while @pos < len(@string)
    begin
        select @char = substring(@string, @pos, 1)
        if ascii(@char) < 32 or ascii(@char) > 126 
            begin
                if @checkExtendedCharset = 1
                    begin
                        if ascii(@char) not in (9,124,130,138,142,146,150,154,158,160,170,176,180,181,183,184,185,186,192,193,194,195,196,197,199,200,201,202,203,204,205,206,207,209,210,211,212,213,214,216,217,218,219,220,221,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,248,249,250,251,252,253,254,255)
                            begin
                                select @return = 1;
                                select @pos = (len(@string) + 1)
                            end
                        else
                            begin
                                select @pos = @pos + 1
                            end
                    end
                else
                    begin
                        select @return = 1;
                        select @pos = (len(@string) + 1)    
                    end
            end
        else
            begin
                select @pos = @pos + 1
            end
    end

    return @return;

end

용도:

select Address1 
from PropertyFile_English
where udf_ContainsNonASCIIChars(Address1, 1) = 1

언급URL : https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server

'programing' 카테고리의 다른 글

구면에서 n개의 점을 고르게 분포 (0)	2023.07.15
마리아와의 부분적인 말다툼DB (0)	2023.07.15
Oracle(및 MySQL)에서 파생 열 목록을 표현하기 위한 일반적인 해결 방법이 있습니까? (0)	2023.07.15
'org. 스프링 프레임워크' 유형의 콩을 정의하는 것을 고려합니다.security.oauth2.client.registration.사용자 구성의 '반응형 클라이언트 등록 리포지토리' (0)	2023.07.15
호스트된 구현에서 크기(int)가 1이 될 수 있습니까? (0)	2023.07.10

현재글SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

각종 프로그래밍 정보를 다루는 블로그입니다.

jquery, sql-server, WordPress, powershell, Excel, bash, c, JSON, reactjs, MariaDB, ajax, spring-boot, ASP.NET, GIT, python, MongoDB, Oracle, Android, angular, AngularJS,

Today :
Yesterday :

linuxpc

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2024/07 »
일	월	화	수	목	금	토
	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

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

SQL Server를 사용하여 varchar 열에서 ASCII가 아닌 문자 찾기

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바