SQLCMD中-y与-W选项互斥时，如何设置显示宽度并去除尾随空格？

Since -W and -y are mutually exclusive in SQLCMD, here are practical workarounds to achieve both goals:

1. Modify Your SQL Query to Trim Trailing Spaces #

Skip relying on SQLCMD's -W option and use SQL's RTRIM() function to remove trailing spaces directly from column data in your query. This ensures clean output while using -y 8000 to display full-length columns:

SELECT 
    RTRIM(Column1) AS Column1,
    RTRIM(LongColumn) AS LongColumn, -- Retains your 1500-character column data
    RTRIM(Column3) AS Column3
FROM YourTable;

Run the query with the -y 8000 flag:

sqlcmd -S host,port -d sid -U user_name -P Password -y 8000 -Q "SELECT RTRIM(Column1), RTRIM(LongColumn) FROM YourTable;"

2. Use Column Separators for Clean, Parsable Output #

Use the -s option to define a specific column separator (like comma or tab) to avoid messy extra spaces between columns. Combine this with -y 8000 to retain long column data:

Example for CSV Output: #

sqlcmd -S host,port -d sid -U user_name -P Password -y 8000 -s "," -h -1 -Q "SELECT Column1, LongColumn FROM YourTable;"

• -s "," sets comma as the column separator
• -h -1 removes the header row (omit this flag if you need headers)

For trimmed fields, add RTRIM() to your query as shown in Solution 1.

3. Post-Process Output with Command-Line Tools #

If you don't want to modify your query, use command-line tools to clean up output after running SQLCMD with -y 8000:

Windows PowerShell: #

Trim trailing spaces and collapse extra spaces between columns:

sqlcmd -S host,port -d sid -U user_name -P Password -y 8000 | ForEach-Object { ($_ -split '\s{2,}').Trim() -join ' ' }

Linux/macOS (using sed): #

Remove trailing spaces from each line:

sqlcmd -S host,port -d sid -U user_name -P Password -y 8000 | sed 's/[[:space:]]*$//'

To also collapse extra spaces between columns:

sqlcmd -S host,port -d sid -U user_name -P Password -y 8000 | sed -e 's/[[:space:]]*$//' -e 's/  */ /g'

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