Trim function in a view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trim function in a view

Author

Topic

btamulis
Yak Posting Veteran

64 Posts

Posted - 2012-05-02 : 11:52:58

I have a view which is used by an Access application - the access developer requested that I trim the text fields......

Example - sop10200.itemnmbr in SQL has 30 characters......

In view I simply use basic sql syntax:

ALTER view [dbo].[ProdBoard1] as select sop10100.sopnumbe as OrderNo, CONVERT (varchar (10), sop10100.docdate, 101) as OrderDate,
sop10100.slprsnid as SalesPersonID, ISNULL (sop10100.pymtrmid, '') as PaymentTerms, sop10100.docamnt as TotalOrderValue, sop10200.itemnmbr as ItemNo,
sop10200.itemdesc as ItemDescription, sop10200.quantity as Qty, sop10200.XTNDPRCE as ExtendedItemPrice, CONVERT(varchar (10),
sop10200.ReqShipDate, 101) as RequestedShipDate, sop10200.ShipToName as ShipTo, sop10200.ADDRESS1 as ShipAddress, sop10200.CITY as ShipCity,
sop10200.STATE as ShipState, sop10200.ZIPCODE as ShipZip,
ISNULL (sop10202.cmmttext, '') as ItemLineNotes, ISNULL (sop10106.cmmttext, '') as OrderNotes
from sop10100
left outer join sop10200 on sop10100.sopnumbe = sop10200.sopnumbe
left outer join sop10202 on sop10100.sopnumbe = sop10202.sopnumbe and sop10200.LNITMSEQ = sop10202.LNITMSEQ
left outer join sop10106 on sop10100.sopnumbe = sop10106.sopnumbe
where sop10100.soptype = '2' and sop10100.voidstts = '0'

how would I specify the sop10200.itemnmbr field be 'trimmed' so that the view would not have any 'empty spaces' - I assume his application picks up the non used remainder of 30 charcters...ie....itemnumber is actually 10 characters - he's seeing 10 plus 20 empty charcters......?

any guidance is appreciated......

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-05-02 : 12:36:46

LTRIM(RTRIM(column name))