0

I have a stored procedure that queries a linked DB2 database table. The query is one big string, though, like so:

SET @sql = 'SELECT * FROM openquery(TRAP_DB2, ''SELECT...'')'

exec sp_executesql @sql

The problem is that the results return a bunch of white space because apparently the DB2 folks used CHAR instead of VARCHAR, forcing columns to be a certain length.

How can I trim the results of my query so it's not returning all the unneeded white space?

blacksaibot
  • 265
  • 2
  • 12

1 Answers1

0

According to the DB2 Documentation you need the TRIM function:

>>-TRIM--(--+---------------------------------------+--string-expression--)-><
            | .-BOTH-----.                          |                         
            '-+----------+--+---------------+--FROM-'                         
              +-B--------+  '-trim-constant-'                                 
              +-LEADING--+                                                    
              +-L--------+                                                    
              +-TRAILING-+                                                    
              '-T--------'

So in your case:

SELECT TRIM(<Your Field Name Here>)
FROM <Your Table Here>
iamdave
  • 12,023
  • 3
  • 24
  • 53
  • Yeah, I tried that, and unfortunately there seems to be a permissions error of some kind because it doesn't work. Cannot get the column information from OLE DB provider "IBMDADB2.DB2COPY1" for linked server "TRAP_DB2". – blacksaibot Jan 10 '17 at 16:41
  • @blacksaibot Details of what you have already tried and the problems you are facing are very helpful for people trying to help you with your problem. Please add these details to your question, as well as any others around your situation. – iamdave Jan 11 '17 at 09:26