How to Improve This Mailing Address SQL Server Select Statement

How can I improve this Mailing Address SQL Server SELECT Statement?

The way to do this is with an UNPIVOT. Here is the solution:

With AddrTable as (
Select AddrFld, MailAddr From (
Select Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
       Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
       Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
       Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
From TableName Where UniqueID=@UniqueID) p
Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
Select Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
MailAddr From AddrTable 
Order By RN

Here's the output:

Address1
Westby WI  55555
-empty line-
-empty line-

Note that I had to use "Varchar(102)" as the field length (unpivot requires that all fields be the same) because your City/Region/Postal can have up to 102 chars in total. Also, note that "@UniqueID" is the identifier for the record whose address you need. This returns four and always four rows containing the data you need for your address.

UPDATE: If you need to return this as a set of four columns rather than four rows, then just plop it into a view and then query the view with a Pivot. I've included the view here for completeness as I had to change the above just a bit when creating the view so the uniqueID field was included and no sort was done (the sort is now done in the query):

Create View AddressRows AS
 With AddrTable as (
 Select UniqueID, AddrFld, MailAddr From (
 Select UniqueID, 
       Cast(ISNULL([Line1], '') as Varchar(102)) as [A1], 
       Cast(ISNULL([Line2], '') as Varchar(102)) as [A2], 
       Cast(ISNULL([Line3], '') as Varchar(102)) as [A3], 
       Cast(ISNULL(LTRIM(RTRIM(City)),'') + ' ' + ISNULL(LTRIM(RTRIM(RegionCode)),'') + '  ' + ISNULL(LTRIM(RTRIM(PostalCode)),'') as Varchar(102)) as A4
 From TableName Where UniqueID=@UniqueID) p
 Unpivot (MailAddr For AddrFld in ([A1], [A2], [A3], [A4])) as unpvt)
 Select UniqueID, 
       Row_Number() over (Order by (Case Len(MailAddr) When 0 then 1 else 0 end), AddrFld) as RN, 
       MailAddr From AddrTable 

And then, when you want to pull your matching "row" out, Pivot it back using this SQL (notice that I am querying again using UniqueID):

Select [Addr1], [Addr2], [Addr3], [Addr4] From (
Select Top 4 'Addr' + Cast(Row_Number() over (Order by RN) as Varchar(12)) as AddrCol,  -- "Top 4" needed so we can sneak the "Order By" in 
MailAddr 
From AddressRows Where UniqueID=@UniqueID
) p PIVOT (Max([MailAddr]) for AddrCol in ([Addr1], [Addr2], [Addr3], [Addr4])
) as pvt

This returns:

Addr1            Addr2                Addr3           Addr4
--------------   ------------------   -------------   ------------------ 
Address1         Westby WI  54667                                                 

Oracle conditional select based on address type

with CTE as
(
select Person_id, 
       Address_Type_Name,
       Address_Info -- replace with your real column names
from Address_Table
where Address_Type_Name in ('Home','Mailing')
)
select Person_id, Address_info
from CTE  a1
where Address_Type_Name = 'Home'
and not exists (select 1 
                from CTE a2 
                where a2.Address_Type_Name = 'Mailing'
                and a2.Person_id = a1.Person_id)
union 
select Person_id, Address_info
from CTE  a1
where Address_Type_Name = 'Mailing'

MS-SQL List of email addresses LIKE statement/regex

It's ugly but it works. Case statement compares number of occurences of @ symbol with number of occurences of @mycompany.com (XXX.. is just for keeping the length of the string):

select 
    * 
    , flag = case when len(field) - len(replace(replace(field,'@mycompany.com','XXXXXXXXXXXXXX'),'@','')) > 0 then 1 else 0 end 
from (
    select 'alice@mycompany.com, bob@mycompany.com, malory@yourcompany.com' as field union all 
    select 'alice@mycompany.com, bob@mycompany.com' union all 
    select 'malory@yourcompany.com' 
) x

How do I UPDATE from a SELECT in SQL Server?

UPDATE
    Table_A
SET
    Table_A.col1 = Table_B.col1,
    Table_A.col2 = Table_B.col2
FROM
    Some_Table AS Table_A
    INNER JOIN Other_Table AS Table_B
        ON Table_A.id = Table_B.id
WHERE
    Table_A.col3 = 'cool'

---

Related Topics