Shell脚本运行T-SQL时结果出现多余>符号，如何去除？

通过Shell脚本调用tsql执行T-SQL语句生成报表时，结果中频繁出现1> 2> 3>这类交互提示符，同时还有冗余的locale信息，干扰数据读取。以下是原脚本及执行输出：

原脚本 #

#!/bin/bash

result=`tsql -S devserver -p 6230 -U user -P password <<EOF

USE devdb
go

select top 1 SampleDate "LastSampleDate" from close with(nolock) order by SampleDate asc
go
select top 1 SampleDate "TopSampleDate" from close_history with(nolock)  order by SampleDate desc
go
select count(1) "history_count" from close_history with(nolock)
GO
select count(1) "count" from close with(nolock)
GO
select convert(varchar, dateadd(day, -190, GETDATE()), 23) "LastDayAfterRunInHistory"
GO
exit
EOF`

echo "now iterate ..."
let rec=0

while read line; do
      echo "processing $rec: $line"
            let rec=rec+1
done <<< "${result}"

原执行输出 #

now iterate ...
processing 0: locale is "en_US.UTF-8"
processing 1: locale charset is "UTF-8"
processing 2: using default charset "UTF-8"
processing 3: locale is "en_US.UTF-8"
processing 4: locale charset is "UTF-8"
processing 5: using default charset "UTF-8"
processing 6: 1> 2> 3> 1> 2> 3> LastSampleDate
processing 7: 2022-01-11
processing 8: (1 row affected)
processing 9: 1> 2> TopSampleDate
processing 10: 2022-01-10
processing 11: (1 row affected)
processing 12: 1> 2> history_count
processing 13: 13824580
processing 14: (1 row affected)
processing 15: 1> 2> count
processing 16: 2857074
processing 17: (1 row affected)
processing 18: 1> 2> LastDayAfterRunInHistory
processing 19: 2022-02-03
processing 20: (1 row affected)
processing 21: 1>

解决方法 #

方法1：使用tsql的非交互模式参数 #

tsql提供-X参数禁用命令行提示符，搭配-m控制消息输出级别过滤冗余信息。修改后的调用命令如下：

result=$(tsql -S devserver -p 6230 -U user -P password -X -m warning <<EOF
USE devdb
go
select top 1 SampleDate "LastSampleDate" from close with(nolock) order by SampleDate asc
go
select top 1 SampleDate "TopSampleDate" from close_history with(nolock)  order by SampleDate desc
go
select count(1) "history_count" from close_history with(nolock)
GO
select count(1) "count" from close with(nolock)
GO
select convert(varchar, dateadd(day, -190, GETDATE()), 23) "LastDayAfterRunInHistory"
GO
exit
EOF)

• -X：关闭命令行编辑功能及提示符输出
• -m warning：仅输出警告及以上级别的消息，自动过滤locale类提示

方法2：用sed过滤输出结果 #

如果无法调整tsql参数，可在获取结果时直接用sed清理冗余内容：

result=$(tsql -S devserver -p 6230 -U user -P password <<EOF
USE devdb
go
select top 1 SampleDate "LastSampleDate" from close with(nolock) order by SampleDate asc
go
select top 1 SampleDate "TopSampleDate" from close_history with(nolock)  order by SampleDate desc
go
select count(1) "history_count" from close_history with(nolock)
GO
select count(1) "count" from close with(nolock)
GO
select convert(varchar, dateadd(day, -190, GETDATE()), 23) "LastDayAfterRunInHistory"
GO
exit
EOF | sed -E '
    s/[0-9]+>//g;          # 移除所有数字加>的提示符
    s/^[[:space:]]*//;     # 清理行首空格
    /^locale is/d;         # 删除locale相关行
    /^using default charset/d;
    /^1>$/d;               # 删除最后一行的空提示符
')

处理后结果仅保留有效数据和必要的行影响提示。

方法3：合并SQL语句减少GO块 #

减少GO的使用次数，将多个SELECT语句合并到同一批处理中，降低提示符出现频率：

result=$(tsql -S devserver -p 6230 -U user -P password <<EOF
USE devdb
GO
select top 1 SampleDate "LastSampleDate" from close with(nolock) order by SampleDate asc;
select top 1 SampleDate "TopSampleDate" from close_history with(nolock)  order by SampleDate desc;
select count(1) "history_count" from close_history with(nolock);
select count(1) "count" from close with(nolock);
select convert(varchar, dateadd(day, -190, GETDATE()), 23) "LastDayAfterRunInHistory";
GO
exit
EOF)

此方式下提示符仅在批处理开始前出现一次，再配合方法1或2的过滤，结果会更简洁。

内容的提问来源于stack exchange，提问作者Sushil