Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 858
  • Last Modified:

White space issue with SQL concatenate statement

Ok, hopefully this is an easy one for you. The following is a part of a SQL statement I am running.

LTRIM(RTRIM(LTRIM(RTRIM(ABCHCD) + ' ' + CAST(ABABTX AS VARCHAR(10)) + '' + RTRIM(ABACCD) + ' ' + RTRIM(ABAECD)+ ' ' + RTRIM(ABFXCD) + ' ' + RTRIM(ABDFCD))))


ABFXCD reperesents a field that does not always have a value. If ABDFCD ALSO does not have a value then I have no issues. However if ABDFCD does have a value there are two spaces before ABDFCD instead of one.

Example:

100 MORTIER DR  1001 (What I am getting)
100 MORTIER DR 1001  (What I want)

Any suggestions on how to alter the code to make this work?
0
CSTX_Analysts
Asked:
CSTX_Analysts
1 Solution
 
chapmandewCommented:
use ltrim and rtrim on the values like you did the first field.
0
 
BrandonGalderisiCommented:
Since you only want to concatenate field + ' ' when the length is >0...

case when datalength(ABCHCD)>0 then abac ABCHCD cd + ' ' else '' end +
case when datalength(cast(ABABTX as varchar(10)))>0 then cast(ABABTX as varchar(10)) + ' ' else '' end +
case when datalength(abaccd)>0 then abaccd + ' ' else '' end +
case when datalength(ABAECD)>0 then ABAECD + ' ' else '' end +
case when datalength(ABFXCD)>0 then ABFXCD + ' ' else '' end +
case when datalength(ABDFCD)>0 then ABDFCD + ' ' else '' end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now