SQLSERVER 筛选出数据包含特殊字符（制表符、回车符、换行符）的字符型和text类型字段

最新推荐文章于 2024-07-28 03:17:24 发布

曾燕辉

最新推荐文章于 2024-07-28 03:17:24 发布

阅读量4.9k

点赞数

分类专栏： SQLSERVER

本文链接：https://blog.csdn.net/yh_zeng2/article/details/52620876

版权

本文介绍如何在SQLSERVER中筛选出数据中包含制表符、回车符、换行符的字符型和text类型字段。通过拼接查询语句并使用CASE语句进行条件判断，对查询结果进行分拆与汇总。

摘要由CSDN通过智能技术生成

1、首先拼接查询语句

-- 在查询出来的结果后面加上 ' null end  as has_special_character '
select 
    case when data_type  like '%text%'  then
              ' case when CAST('+column_name+' AS varchar(8000)) like ''%''+CHAR(10)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(9)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(13)+''%'' then '''+column_name+''' else '                 
         else
              ' case when '+column_name+' like ''%''+CHAR(10)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(9)+''%'' or CAST('+[column_name]+' AS varchar(8000)) like ''%''+CHAR(13)+''%'' then '''+column_name+''' else '         
    end 'qu'
    -- column_name  +  ',' as colname
 from information_schema.columns
where table_name = 'MChannel'  
and ((CHARACTER_MAXIMUM_LENGTH is not null or data_type = 'uniqueidentifier') or data_type  like '%text%' )

2、由于SQLSERVER只能嵌套10个case when 语句，所以步骤1查询出来的脚本要进行分拆，如下：

1）步骤一的查询结果：

 case when chan_Name like '%'+CHAR(10)+'%' or CAST(chan_Name AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_Name AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_Name' else 
 case when chan_Status like '%'+CHAR(10)+'%' or CAST(chan_Status AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_Status AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_Status' else 
 case when chan_reference like '%'+CHAR(10)+'%' or CAST(chan_reference AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_reference AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_reference' else 
 case when chan_type like '%'+CHAR(10)+'%' or CAST(chan_type AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_type AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_type' else 
 case when chan_purpose like '%'+CHAR(10)+'%' or CAST(chan_purpose AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_purpose AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_purpose' else 
 case when CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(10)+'%' or CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_scheme AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_scheme' else 
 case when CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(10)+'%' or CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(9)+'%' or CAST(chan_competitor AS varchar(8000)) like '%'+CHAR(13)+'%' then 'chan_competitor' else 
 case when chan_contractstatus like &#