[SOLVED] Remove spaces from right and left (TRIM) all selected columns

Remove spaces from right and left (TRIM) all selected columns

I have went through some documentations and as it states SELECT TRIM('') FROM something; can be used to trim strings.

I have SQL command to get the table:

"select NRO,SNAME,NAMEA,NAMEB,ADDRESS,POSTS,POSTN,POSTTP,COMPANY,COUNTRY,BID from COMPANY where ACTIVE = '1' AND NRO Like '7%'"

So I have tried to use:

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

but this is throwing an error. What is the proper way of using TRIM in this case? I need to remove spaces from left and right but leave them in between if there are any.

Error message:

Client Interface][LNA][PSQL][SQL Engine]Error in expression: TRIM ( NRO ) ERROR [HY000] [PSQL][ODBC Client Interface][LNA][PSQL][SQL Engine][Data Record Manager]Invalid user-defined or scalar function.'

Solution

You can use LTRIM and RTRIM functions which removes starting and ending spaces for the SQL Server prior to 2017.

So, your query becomes:

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

For the SQL Server 2017 and later you can use TRIM function as Dale K suggested.