0

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.'

10101
  • 2,232
  • 3
  • 26
  • 66

1 Answers1

1

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.

Nazim
  • 639
  • 2
  • 10
  • 26
  • Seems that is newly added function, documentation says it applies to SQL Server 2017 and later versions. – Nazim May 30 '20 at 09:31
  • Pretty sure OP is using MySQL anyway... (2017 is already 3 years ago :) ) – Dale K May 30 '20 at 09:32
  • Thank you for this! Now I don't receive any error but it looks like running forever. Still waiting for any data output to see the result. – 10101 May 30 '20 at 09:42
  • @hatman it might be something related to your table's performance. How many records does it have in total? Try to test with less data like below: select top 1 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 – Nazim May 30 '20 at 09:45
  • You are using a `LIKE` statement for **NRO** column, it might be creating performance issue. Do you have any index on it? – Nazim May 30 '20 at 09:49
  • For some reason even with `select top 1` it doesn't work. It just keeps going. After removing `RTRIM(LTRIM())` everything works. I can of course trim later in C# code, was just thinking maybe it can be done directly in SQL request. Also as already mentioned before `TRIM()` seems to be causing an errors. – 10101 Jun 01 '20 at 10:21
  • I narrowed the query to one column only, try the below statement: `select TOP 1 RTRIM(LTRIM(NRO)) from COMPANY WITH(NOLOCK)` – Nazim Jun 01 '20 at 14:12