PSQL中对多列使用TRIM函数报错，求助正确实现方式

It looks like you're hitting a syntax quirk specific to Pervasive SQL (PSQL) — unlike standard SQL dialects (like PostgreSQL or MySQL), PSQL doesn't support the shorthand TRIM(column) syntax you tried. That's why you're getting the "Invalid user-defined or scalar function" error.

Here are two valid ways to trim leading/trailing spaces (while preserving internal spaces) in your query:

1. Use LTRIM + RTRIM Combination #

PSQL natively supports LTRIM() (removes leading spaces) and RTRIM() (removes trailing spaces). Nest them to trim spaces from both sides:

select 
  LTRIM(RTRIM(NRO)) as NRO,
  LTRIM(RTRIM(SNAME)) as SNAME,
  LTRIM(RTRIM(NAMEA)) as NAMEA,
  LTRIM(RTRIM(NAMEB)) as NAMEB,
  LTRIM(RTRIM(ADDRESS)) as ADDRESS,
  LTRIM(RTRIM(POSTS)) as POSTS,
  LTRIM(RTRIM(POSTN)) as POSTN,
  LTRIM(RTRIM(POSTTP)) as POSTTP,
  LTRIM(RTRIM(COMPANY)) as COMPANY,
  LTRIM(RTRIM(COUNTRY)) as COUNTRY,
  LTRIM(RTRIM(BID)) as BID
from COMPANY 
where ACTIVE = '1' AND NRO Like '7%'

Adding the as [column-name] aliases ensures your result set keeps the original column names instead of showing the nested function call as the column header.

2. Use PSQL's Full TRIM Syntax #

PSQL requires you to explicitly specify the trim direction and use the FROM keyword when using TRIM(). For trimming both leading and trailing spaces, use TRIM(BOTH FROM column):

select 
  TRIM(BOTH FROM NRO) as NRO,
  TRIM(BOTH FROM SNAME) as SNAME,
  TRIM(BOTH FROM NAMEA) as NAMEA,
  TRIM(BOTH FROM NAMEB) as NAMEB,
  TRIM(BOTH FROM ADDRESS) as ADDRESS,
  TRIM(BOTH FROM POSTS) as POSTS,
  TRIM(BOTH FROM POSTN) as POSTN,
  TRIM(BOTH FROM POSTTP) as POSTTP,
  TRIM(BOTH FROM COMPANY) as COMPANY,
  TRIM(BOTH FROM COUNTRY) as COUNTRY,
  TRIM(BOTH FROM BID) as BID
from COMPANY 
where ACTIVE = '1' AND NRO Like '7%'

If you only needed to trim leading spaces, you'd use TRIM(LEADING FROM column); for trailing spaces alone, TRIM(TRAILING FROM column).

Either approach will resolve the error and give you the trimmed column values you need.

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