CODEWITHSUNDEEP
t-sqldb2openquery
blacksaibot
blacksaibot

Reputation: 285

Trim results from DB2 query

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?

Upvotes: 0

Views: 1196

Answers (1)

iamdave
iamdave

Reputation: 12243

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>

Upvotes: 0

Related Questions