CODEWITHSUNDEEP
sqlsql-server
Rajee Kasthuri
Rajee Kasthuri

Reputation: 159

White-space between two values

I have a column as like given below

EB.Liter+'L'+EB.CC+'cc'+'-ci'+EB.BlockType+EB.Cylinders+
         (
       EB.EngBoreIn+'x'+
        EB.EngStrokeIn+';'+
        EB.EngBoreMetric+'x'+
    EB.EngStrokeMetric
         ) as EngineBase

This pull data something like this

1.3   L 1339    cc L 4 (2.87      x3.15      ;73.0      x80.0      )

but i am trying to get something like

1.3L 1339cc -ci L4 (2.87x3.15; 73.0x80.0)

may anyone please suggest if possible

i have tried something like this.

LTRIM(RTRIM(EB.Liter))+'L'+LTRIM(RTRIM(EB.CC))+'cc'+'-  
ci'+LTRIM(RTRIM(EB.BlockType))+LTRIM(RTRIM(EB.Cylinders))+
        + (
       LTRIM(RTRIM(EB.EngBoreIn))+'x'+
        LTRIM(RTRIM(EB.EngStrokeIn))+';'+
        LTRIM(RTRIM(EB.EngBoreMetric))+'x'+
    LTRIM(RTRIM(EB.EngStrokeMetric))
         ) as EBase

which get me result as below 1.3L1339cc-ciL42.87x3.15;73.0x80.0

but correct output is as 1.3L 1339cc -ciL4(2.87x3.15; 73.0x80.0)

Upvotes: 0

Views: 76

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

modify like this

LTRIM(RTRIM(EB.Liter))+'L '+LTRIM(RTRIM(EB.CC))+'cc '+'-ci'+LTRIM(RTRIM(EB.BlockType))+LTRIM(RTRIM(EB.Cylinders))+
    + '('+
    LTRIM(RTRIM(EB.EngBoreIn))+'x'+
    LTRIM(RTRIM(EB.EngStrokeIn))+'; '+
    LTRIM(RTRIM(EB.EngBoreMetric))+'x'+
    LTRIM(RTRIM(EB.EngStrokeMetric))
    +')' as EBase

Upvotes: 1

bdifferent
bdifferent

Reputation: 703

you can use trim functionality to remove leading and trailing spaces. Your query would look like that then:

TRIM(EB.Liter) + 'L'+TRIM(EB.CC)+'cc'+'-ci'+TRIM(EB.BlockType)+TRIM(EB.Cylinders)+
(
TRIM(EB.EngBoreIn)+'x'+
TRIM(EB.EngStrokeIn)+';'+
TRIM(EB.EngBoreMetric)+'x'+
TRIM(EB.EngStrokeMetric)
) as EngineBase

Upvotes: 0

Related Questions