SQL Server - Supprimer tous les non-caractères ASCII imprimables

Nous avons récemment migré à partir de SQL Server 2012 SQL Server 2014 et tous nos FOR XML code commencé à lancer des erreurs au sujet de la non-caractères ASCII imprimables.
J'ai écrit ce horrible fonction pour supprimer des caractères ASCII imprimables comme une solution rapide. Je veux le remplacer par quelque chose de plus propre. Est-il un moyen de faire cela?

ALTER FUNCTION [dbo].[remove_non_printable_chars]
(@input_string nvarchar(max))
RETURNS nvarchar(max)
BEGIN
RETURN
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(@input_string,
CHAR(1), ''),CHAR(2), ''),CHAR(3), ''),CHAR(4), ''),CHAR(5), ''),CHAR(6), ''),CHAR(7), ''),CHAR(8), ''),CHAR(9), ''),CHAR(10), ''),
CHAR(11), ''),CHAR(12), ''),CHAR(13), ''),CHAR(14), ''),CHAR(15), ''),CHAR(16), ''),CHAR(17), ''),CHAR(18), ''),CHAR(19), ''),CHAR(20), ''),
CHAR(21), ''),CHAR(22), ''),CHAR(23), ''),CHAR(24), ''),CHAR(25), ''),CHAR(26), ''),CHAR(27), ''),CHAR(28), ''),CHAR(29), ''),CHAR(30), ''),
CHAR(31), ''), NCHAR(0) COLLATE Latin1_General_100_BIN2, '')
END

Voici la FOR XML code qui s'est brisé. (Je n'ai pas écrit cela. Il était déjà dans la base de code).

SELECT @HTMLTableData =
(
SELECT  HTMLRows 
FROM (
SELECT N'<tr>' + HTMLRow + N'</tr>' AS HTMLRows 
FROM @HTMLRowData
) mi            
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)')
C'est un peu lourd mais remplacer imbriquée est ce qui va se passer pour supprimer ces caractères spécifiques. Remplacer imbriquée est aussi super rapide. Vous pourriez envisager de le transformer en une ligne de table d'une valeur de la fonction au lieu d'une fonction scalaire. FWIW, je n'ai pas cette fonction est horrible. Il y a beaucoup de laid code qui est nécessaire....type de point de fonctions en premier lieu. 🙂
J'avais ce post sur codereview.stackexchange.com
La recherche sur le Web a donné: sqlservercentral.com/Forums/Topic860321-338-1.aspx
Merci pour le compliment 🙂
Cette phrase me fait penser: Nous avons récemment migré à partir de SQL Server 2012 SQL Server 2014 et tous nos POUR de code XML ont commencé à jeter des erreurs à propos de la non-caractères ASCII imprimables. Pourquoi et quelles erreurs? Comment êtes-vous créer votre XMLs? Cela ne devrait pas arriver... Lisez à propos de la XY-Problème, Il pourrait aider à montrer quelques exemples de ce qui a fonctionné dans SS12 et ne fonctionne pas avec l'es14. Je suis tout à fait sûr, que le problème et sa solution! - est ailleurs...

OriginalL'auteur Munir | 2017-03-31

  1. 7

    Une Autre Option.

    Cette fonction permettra de remplacer les caractères de contrôle et de corriger tout résidu de répéter des espaces. Par exemple Jane Smith{13}was here ne seront pas retournés comme Jane Smithwas here, mais plutôt Jane Smith was here

    CREATE FUNCTION [dbo].[udf-Str-Strip-Control](@S varchar(max))
Returns varchar(max)
Begin
;with  cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(C) As (Select Top (32) Char(Row_Number() over (Order By (Select NULL))-1) From cte1 a,cte1 b)
Select @S = Replace(@S,C,' ')
From  cte2
Return ltrim(rtrim(replace(replace(replace(@S,' ','†‡'),'‡†',''),'†‡',' ')))
End
--Select [dbo].[udf-Str-Strip-Control]('Michael        '+char(13)+char(10)+'LastName')  --Returns: Michael LastName
    Merci. Je vais essayer cela demain. Je ne suis pas sûr que je veux ajouter des espaces, bien que depuis les caractères de contrôle peuvent être à l'intérieur d'un mot.
    juste assez, alors il suffit de changer Select @S = Replace(@S,C,' ') pour Sélectionner @S = Replace(@S,C,") cependant, vous courez le risque de concatinating mots
    je vois que vous n'enregistrez que Gordon Linoff magie avec >< <> 🙂
    Pouvez-vous me blâmer? Quand j'ai vu pour la première fois, ma première pensée a été "Meh...", puis il m'est apparu... F n génial !
    Tire pas. Je me souviens encore de cette réponse, et a été soufflé, d'où mon souvenir maintenant!

    OriginalL'auteur John Cappelletti

  2. 7

    En ligne version:

    create function [dbo].[remove_non_printable_chars] (@input_string nvarchar(max))
returns table with schemabinding as return (
select 
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(@input_string collate latin1_general_100_bin2,
char(1), ''),char(2), ''),char(3), ''),char(4), ''),char(5), ''),char(6), ''),char(7), ''),char(8), ''),char(9), ''),char(10), ''),
char(11), ''),char(12), ''),char(13), ''),char(14), ''),char(15), ''),char(16), ''),char(17), ''),char(18), ''),char(19), ''),char(20), ''),
char(21), ''),char(22), ''),char(23), ''),char(24), ''),char(25), ''),char(26), ''),char(27), ''),char(28), ''),char(29), ''),char(30), ''),
char(31), ''), char(0) , '') 
as clean_string
);
go

    Et de l'utiliser comme ceci: 

    select c.clean_string
from dbo.remove_non_printable_chars(@dirtystring) c

    ou

    select ...
, c.clean_string
from t
cross apply dbo.remove_non_printable_chars(t.dirty_string) c

    Référence:

    Merci...je vais attendre d'autres réponses. Si non, je vais juste passer à une fonction à valeur de table.
    Semble que vous en avez downvoted par le même gars que downvoted moi. Je me demande ce qu'il sait que nous avons manqué. 🙂
    Je suppose que nous ne saurons jamais
    Pouvez-vous élaborer sur l'utilisation de collate latin1_general_100_bin2? Au début, j'ai essayé de le supprimer parce que ma base de données dispose d'un classement différent, mais j'ai remarqué que pour une requête avec ~3000 lignes, la fonction est beaucoup plus rapide avec collate latin1_general_100_bin2 (2 secondes par rapport à 30 secondes)...et je ne comprends pas pourquoi.
    Je suis inquiet au sujet de la requête de prendre 30 secondes pour s'exécuter plus de 3000 lignes, même sans le classement de l'interrupteur. Quelle est la taille de ces chaînes que vous utilisez? Je n'aurais pas cru que les grandes d'une différence. lignes Directrices pour l'Utilisation de la CORBEILLE et BIN2 Classements et Paul Blanc de la réponse à propos de classement binaire de la performance

    OriginalL'auteur SqlZim

  3. 0

    À l'aide d'une combinaison de REMPLACER,PATINDEX et les expressions régulières, vous pouvez obtenir ce que vous demandez

    Pour montrer un exemple de travail permet de Créer un Tableau pour stocker les chaînes avec les Caractères ASCII imprimables

    CREATE TABLE ##NoPrintableStrings
(
BadStrings VARCHAR(20)
)
GO

    Insérer des chaînes de caractères non imprimables ASCII des Caractères dans la table créée

    INSERT ##NoPrintableStrings SELECT ‘The quick ‘ + CHAR(10) + ‘ “brown’
INSERT ##NoPrintableStrings SELECT ‘fox jumped ‘ + CHAR(11) + ‘ ‘
INSERT ##NoPrintableStrings SELECT CHAR(12) + ‘ over the ‘
INSERT ##NoPrintableStrings SELECT ‘ log ‘ + CHAR(13)
INSERT ##NoPrintableStrings SELECT ‘O”Keefe’
— Show the data in the table
SELECT badstrings FROM ##NoPrintableStrings

    Mise à jour de la table de supprimer tout non alphanumerice caractères en utilisant le caractère générique functioanlity de la PATINDEX fonction

    UPDATE  ##NoPrintableStrings
SET badstrings = REPLACE(badstrings, SUBSTRING(badstrings, PATINDEX(‘%[^a-zA-Z0-9 ”””]%’, badstrings), 1), ”)
WHERE PATINDEX(‘%[^a-zA-Z0-9 ”””]%’, badstrings) <> 0

    — Montrent que la non alphanumerice personnages ont été supprimés

    SELECT badstrings FROM ##NoPrintableStrings

    Tirée du https://www.wardyit.com/blog/remove-non-printable-characters-from-a-string/

    Hey, merci pour la réponse, mais je tiens à souligner que cela ne fonctionne pas dans un environnement international (jeux de caractères non anglais). Aussi, votre patron est en acceptant uniquement des alphabets, des chiffres, de l'espace et de citations, qui n'est pas assez bon, même pour l'anglais. Des choses comme la période, des virgules, des points-virgules, parenthèses, etc. sera également supprimé ici.
    eh bien, vous pouvez adapter la PATINDEX expression régulière à vos besoins et à votre langue et il fonctionnera
    Je ne sais pas pour vous, mais je ne veux pas la liste de tous les emoji qui existe dans une regexp. Cela ne fonctionne que pour des cas très spécifiques où le jeu de caractères des entrées connues.

    OriginalL'auteur Mauricio Gracia Gutierrez