CODEWITHSUNDEEP
sqlsql-server
Simulator23
Simulator23

Reputation: 77

RTRIM & LTRIM Function with Case Statement

How do i use the LTRIM & RTRIM with the following SQL? I need to LTRIM and RTRIM all these fields for leading spaces

UPDATE CORE.WeccoPartyAddress 
SET AddressElements = CONTROL.TrimChar(
                                   CASE when COALESCE(Address1,'') != '' THEN Address1 + ', ' ELSE '' END +
                                   CASE when COALESCE(Address2,'') != '' THEN Address2 + ', ' ELSE '' END +
                                   CASE when COALESCE(Address3,'') != '' THEN Address3 + ', ' ELSE '' END +
                                   CASE when COALESCE(Town,'') != '' THEN Town + ', ' ELSE '' END +
                                   CASE when COALESCE(County,'') != '' THEN County + ', ' ELSE '' END +
                                   CASE when COALESCE(Postcode,'') != '' THEN Postcode ELSE '' END, ', '
                                  )

Upvotes: 0

Views: 2593

Answers (2)

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

You don't have to use CASE in your statement

UPDATE CORE.WeccoPartyAddress
SET AddressElements = ISNULL( STUFF (
    COALESCE( ', ' + LTRIM( RTRIM(Address1) )           , '') +
    COALESCE( ', ' + LTRIM( RTRIM(Address1Address2) )   , '') +
    COALESCE( ', ' + LTRIM( RTRIM(Address1Address3) )   , '') +
    COALESCE( ', ' + LTRIM( RTRIM(Address1Town) )       , '') +
    COALESCE( ', ' + LTRIM( RTRIM(Address1County) )     , '') +
    COALESCE( ', ' + LTRIM( RTRIM(Address1Postcode) )   , '')
    ,1
    ,2
    ,''
    ), '')

If any of Address values is not null you will get string like this: ', Address', then using the function STUFF you replace ', ' at the beginning of the string to get 'Address' as the result.

If all values are null the STUFF function will return NULL which will be replaced with '' by ISNULL function.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use like below nested:

UPDATE CORE.WeccoPartyAddress SET AddressElements = rtrim(ltrim(CASE when COALESCE(Address1,'') != '' THEN Address1 + ', ' ELSE '' END + CASE when COALESCE(Address2,'') != '' THEN Address2 + ', ' ELSE '' END + CASE when COALESCE(Address3,'') != '' THEN Address3 + ', ' ELSE '' END + CASE when COALESCE(Town,'') != '' THEN Town + ', ' ELSE '' END + CASE when COALESCE(County,'') != '' THEN County + ', ' ELSE '' END + CASE when COALESCE(Postcode,'') != '' THEN Postcode ELSE '' END))

Upvotes: 2

Related Questions