SQL多列拼接去空格及COL1长度适配问题咨询

Hey there! Let's break down your two SQL challenges with practical, actionable solutions:

The exact method depends on what kind of spaces you want to eliminate—here are the most common scenarios for major SQL dialects:

• Strip leading/trailing spaces from each column first
  Use TRIM() to clean up whitespace at the start/end of individual columns, then combine them with CONCAT() (for direct拼接) or CONCAT_WS() (if you need a separator between columns):

  -- For MySQL, PostgreSQL, SQL Server 2017+
  CONCAT(TRIM(COL1), TRIM(COL2), TRIM(COL3))
  
  -- With a custom separator (e.g., hyphen)
  CONCAT_WS('-', TRIM(COL1), TRIM(COL2), TRIM(COL3))
  

• Remove all spaces (including those inside values)
  If you need to eliminate every space in the column data, pair REPLACE() with your concatenation logic:

  CONCAT(REPLACE(COL1, ' ', ''), REPLACE(COL2, ' ', ''))
  

Since your Output #1 works when COL1 is 5 characters but fails at 2, it’s clear your current logic relies on COL1 having a fixed length. To get consistent results no matter how long COL1 is, standardize its width before concatenating.

Assuming you want COL1 to match the 5-character format of your working Output #1, use padding functions to fill the gap:

• Left-pad with spaces (adds spaces to the left to make COL1 5 characters wide):

  -- MySQL/PostgreSQL
  CONCAT(LPAD(COL1, 5, ' '), COL2)
  
  -- SQL Server
  CONCAT(RIGHT('     ' + COL1, 5), COL2) -- 5 spaces inside the quotes
  

• Right-pad with spaces (adds spaces to the right):

  -- MySQL/PostgreSQL
  CONCAT(RPAD(COL1, 5, ' '), COL2)
  
  -- SQL Server
  CONCAT(LEFT(COL1 + '     ', 5), COL2)
  

If you want to clean up existing whitespace first before standardizing, combine padding with TRIM():

CONCAT(LPAD(TRIM(COL1), 5, ' '), TRIM(COL2))

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