Verifica se la stringa contiene caratteri accentati in SQL?

Voglio eseguire un’attività se la stringa di input contiene caratteri accentati, altrimenti esegui un’altra attività in SQL. C’è un modo per verificare questa condizione in SQL?

Per esempio:

@myString1 = 'àéêöhello!' IF(@myString1 contains any accented characters) Task1 ELSE Task2 

SQL Fiddle: 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 

(basato sulla soluzione qui: come posso rimuovere gli accenti su una stringa? )

Io uso questa funzione per ottenere testo (principalmente nomi di famiglie straniere) in un latino semplice più paragonabile (con alcune chicche indivise – sentiti libero di implementare il tuo …).

Forse puoi usare anche questo. Basta confrontare, se la tua stringa è uguale a quella passata indietro.

 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 @txtTransformsd 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(@txtTransformsd),1) AS pos --ASCII-Codes of all characters in your text CROSS APPLY(SELECT ASCII(SUBSTRING(@txtTransformsd,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; 

Un modo approssimativo è controllare ASCII() >= 128 per ogni carattere. Qualcosa come questo

 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)