[SQL] 문자열이 SQL에 악센트 문자가 포함 된 경우 확인?

문자열이 SQL에 악센트 문자가 포함 된 경우 확인?

I는 입력 문자열이 다른 어떤 악센트 문자가 포함 된 SQL에서 다른 작업을 할 경우 작업을 수행합니다. SQL에서이 상태를 확인하는 방법은 없나요?

예 :

@myString1 = 'àéêöhello!'

IF(@myString1 contains any accented characters)
  Task1
ELSE
  Task2

해결법

1. ==============================

   1.SQL 바이올린 : http://sqlfiddle.com/#!6/9eecb7d/1607

   SQL 바이올린 : http://sqlfiddle.com/#!6/9eecb7d/1607

   declare @a nvarchar(32) = 'àéêöhello!'
   declare @b nvarchar(32) = 'aeeohello!'
   
   select case 
       when (cast(@a as varchar(32)) collate SQL_Latin1_General_Cp1251_CS_AS) = @a 
       then 0 
       else 1 
   end HasSpecialChars
   
   select case 
       when (cast(@b as varchar(32)) collate SQL_Latin1_General_Cp1251_CS_AS) = @b 
       then 0 
       else 1 
   end HasSpecialChars
   

   (여기에 솔루션을 기반으로 : 나는 문자열에 악센트를 제거 할 수 있습니까?)

2. ==============================

   2.나는 텍스트 (주로 외국 적 이름)을 얻기 위해이 기능을 사용하여보다 비교 라틴어 일반 (일부 개별 선택 케이크와 함께 - ... 자신의 구현 주시기 바랍니다).

   나는 텍스트 (주로 외국 적 이름)을 얻기 위해이 기능을 사용하여보다 비교 라틴어 일반 (일부 개별 선택 케이크와 함께 - ... 자신의 구현 주시기 바랍니다).

   아마 당신도이를 사용할 수 있습니다. 하나는 다시 전달로 문자열이 동일한 경우 그냥 비교합니다.

   CREATE FUNCTION [dbo].[GetRunningNumbers](@anzahl INT=1000000, @StartAt INT=0)
   RETURNS TABLE
   AS 
   RETURN
       SELECT TOP (ISNULL(@anzahl,1000000)) ROW_NUMBER() OVER(ORDER BY A) -1 + ISNULL(@StartAt,0) AS Nmbr
       FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblA(A)
           ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblB(B)
           ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblC(C)
           ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblD(D)
           ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblE(E)
           ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS tblF(F);
   
   GO
   CREATE FUNCTION [dbo].[GetTextPlainLatin]
   (
        @Txt VARCHAR(MAX)
       ,@CaseSensitive BIT
       ,@KeepNumbers BIT
       ,@NonCharReplace VARCHAR(100),@MinusReplace VARCHAR(100) 
       ,@PercentReplace VARCHAR(100),@UnderscoreReplace VARCHAR(100) --for SQL-Masks
       ,@AsteriskReplace VARCHAR(100),@QuestionmarkReplace VARCHAR(100) --for SQL-Masks (Access-Style)
   )
   RETURNS VARCHAR(MAX)
   AS
   BEGIN
   
   DECLARE @txtTransformed VARCHAR(MAX)=(SELECT LTRIM(RTRIM(CASE WHEN ISNULL(@CaseSensitive,0)=0 THEN LOWER(@Txt) ELSE @Txt END)));
   RETURN
   (
       SELECT Repl.ASCII_Code
       FROM dbo.GetRunningNumbers(LEN(@txtTransformed),1) AS pos
       --ASCII-Codes of all characters in your text
       CROSS APPLY(SELECT ASCII(SUBSTRING(@txtTransformed,pos.Nmbr,1)) AS ASCII_Code) AS OneChar  
       --re-code 
       CROSS APPLY
       (
           SELECT CASE 
               WHEN OneChar.ASCII_Code BETWEEN ASCII('A') AND ASCII('Z') THEN  CHAR(OneChar.ASCII_Code)
               WHEN OneChar.ASCII_Code BETWEEN ASCII('a') AND ASCII('z') THEN  CHAR(OneChar.ASCII_Code)
               WHEN OneChar.ASCII_Code BETWEEN ASCII('0') AND ASCII('9') AND @KeepNumbers=1 THEN CHAR(OneChar.ASCII_Code)
               WHEN OneChar.ASCII_Code = ASCII('ƒ') THEN 'f'
               WHEN OneChar.ASCII_Code = ASCII('Š') THEN 'S'
               WHEN OneChar.ASCII_Code = ASCII('š') THEN 's'
               WHEN OneChar.ASCII_Code = ASCII('ß') THEN 'ss'
               WHEN OneChar.ASCII_Code = ASCII('Ä') THEN 'Ae'
               WHEN OneChar.ASCII_Code = ASCII('ä') THEN 'ae'
               WHEN OneChar.ASCII_Code = ASCII('Æ') THEN 'Ae'
               WHEN OneChar.ASCII_Code = ASCII('æ') THEN 'ae'
               WHEN OneChar.ASCII_Code = ASCII('Ö') THEN 'Oe'
               WHEN OneChar.ASCII_Code = ASCII('ö') THEN 'oe'
               WHEN OneChar.ASCII_Code = ASCII('Œ') THEN 'Oe'
               WHEN OneChar.ASCII_Code = ASCII('œ') THEN 'oe'
               WHEN OneChar.ASCII_Code = ASCII('Ü') THEN 'Ue'
               WHEN OneChar.ASCII_Code = ASCII('ü') THEN 'ue'
               WHEN OneChar.ASCII_Code = ASCII('Ž') THEN 'Z'
               WHEN OneChar.ASCII_Code = ASCII('ž') THEN 'z'
               WHEN OneChar.ASCII_Code = ASCII('×') THEN 'x'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('À') AND ASCII('Å') THEN  'A'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('à') AND ASCII('å') THEN  'a'
               WHEN OneChar.ASCII_Code = ASCII('Ç') THEN 'C'
               WHEN OneChar.ASCII_Code = ASCII('ç') THEN 'c'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('È') AND ASCII('Ë') THEN  'E'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('è') AND ASCII('ë') THEN  'e'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('Ì') AND ASCII('Ï') THEN  'I'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('ì') AND ASCII('ï') THEN  'i'
               WHEN OneChar.ASCII_Code = ASCII('Ð') THEN 'D' --island Eth
               WHEN OneChar.ASCII_Code = ASCII('ð') THEN 'd' --island eth
               WHEN OneChar.ASCII_Code = ASCII('Ñ') THEN 'N'
               WHEN OneChar.ASCII_Code = ASCII('ñ') THEN 'n'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('Ò') AND ASCII('Ö') THEN 'O'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('ò') AND ASCII('ö') THEN 'o'
               WHEN OneChar.ASCII_Code = ASCII('Ø') THEN 'O'
               WHEN OneChar.ASCII_Code = ASCII('ø') THEN 'o'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('Ù') AND ASCII('Ü') THEN  'U'
               WHEN OneChar.ASCII_Code BETWEEN ASCII('ù') AND ASCII('ü') THEN  'u'
               WHEN OneChar.ASCII_Code = ASCII('Ý') THEN 'Y'
               WHEN OneChar.ASCII_Code = ASCII('ý') THEN 'y'
               WHEN OneChar.ASCII_Code = ASCII('Þ') THEN 'Th' --island Thorn
               WHEN OneChar.ASCII_Code = ASCII('þ') THEN 'th' --island thorn
               WHEN OneChar.ASCII_Code = ASCII('Ÿ') THEN 'Y'
               WHEN OneChar.ASCII_Code = ASCII('ÿ') THEN 'y'
               --Special with "minus"
               WHEN OneChar.ASCII_Code = ASCII('-') THEN ISNULL(@MinusReplace,ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code)))
               --Special with mask characters
               WHEN OneChar.ASCII_Code = ASCII('%') THEN ISNULL(@PercentReplace,ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code)))
               WHEN OneChar.ASCII_Code = ASCII('_') THEN ISNULL(@UnderscoreReplace,ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code)))
               WHEN OneChar.ASCII_Code = ASCII('*') THEN ISNULL(@AsteriskReplace,ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code)))
               WHEN OneChar.ASCII_Code = ASCII('?') THEN ISNULL(@QuestionmarkReplace,ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code)))
               --replace others        
               ELSE ISNULL(@NonCharReplace,CHAR(OneChar.ASCII_Code))
           END AS ASCII_Code
       ) AS Repl    
       FOR XML PATH(''),TYPE
   ).value('.','varchar(max)');
   END
   GO
   
   SELECT dbo.GetTextPlainLatin('Case sensitive ÄÖ àéêöhello!',1,1,NULL,NULL,NULL,NULL,NULL,NULL);
   SELECT dbo.GetTextPlainLatin('Case in-sensitive ÄÖ àéêöhello!',0,1,NULL,NULL,NULL,NULL,NULL,NULL);
   GO
   DROP FUNCTION dbo.GetTextPlainLatin
   GO
   DROP FUNCTION dbo.GetRunningNumbers;
   

3. ==============================

   3.조 방법 체크 ASCII (<각 문자>) 각 문자> = 128이다. 이 같은

   조 방법 체크 ASCII (<각 문자>) 각 문자> = 128이다. 이 같은

   DECLARE @MyString NVARCHAR(100)
   SET @MyString = N'àéêöhello!'
   
   ;WITH N as 
   (
   SELECT 1 r UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
   ), Numbers
   as
   (
   SELECT RN = ROW_NUMBER()OVER(ORDER BY N1.r) FROM N as N1 CROSS JOIN N as N2
   )
   SELECT MAX(CASE WHEN ASCII(SUBSTRING(@MyString,RN,1)) >= 128 THEN 1 ELSE 0 END) ContainsAccentedChars FROM Numbers
   WHERE RN <= LEN(@MyString)
   

from https://stackoverflow.com/questions/32046697/check-if-the-string-contains-accented-characters-in-sql by cc-by-sa and MIT license