How to optimiz this slect statement please

Question

How to optimiz this slect statement please

asita

SSCarpal Tunnel

Points: 4144
More actions
April 17, 2018 at 9:57 am

#344781

Hello Good Morning,
How to optimize this code please advise....
SELECT CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2))))ELSEELSE '''' ENDEND asas AddFIELD2 AddFIELD2,, --- Check if any of 3 fields has "C/O" in it otherwise empty--- Check if any of 3 fields has "C/O" in it otherwise empty CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' ENDEND asas AddFIELD3 AddFIELD3,, --- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty--- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty LTRIMLTRIM((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'C/O''C/O' ,,''''),),'c/o''c/o',,''''),),'C/o''C/o',,''''),),'c/O''c/O',,'''')) )) asas WithoutCOAddress1 WithoutCOAddress1,, --- exclude C/O from the fields--- exclude C/O from the fields REPLACEREPLACE((LTRIMLTRIM(( REPLACEREPLACE((REPLACEREPLACE((REPLACEREPLACE ((REPLACEREPLACE(( REPLACEREPLACE(( REPLACEREPLACE(( CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'ATTN''ATTN',,''''),),'attn''attn',,''''),),'Attn''Attn',,''''),),'ATT:''ATT:',,''''),), 'Attention''Attention',,''''),),':'':',,'''')),)),';'';',,'''')) asas WithoutAttnAddress2 WithoutAttnAddress2,, --- exclude ATTN from the fields--- exclude ATTN from the fields CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL THENTHEN addressfield1 addressfield1 WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' THENTHEN ((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((ee..OvrFlowNmOvrFlowNm)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' ANDAND LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) ISIS NOTNOT NULLNULL ANDAND LENLEN((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))))) >> 0 0 THENTHEN addressfield3_O addressfield3_O ELSEELSE ISNULLISNULL((addressfield2addressfield2,,'''')) endend )) ELSEELSE '''' ENDEND asas AddrLine1 AddrLine1,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL ANDAND addressfield2 addressfield2 ISIS NOTNOT NULLNULL THENTHEN addressfield2 addressfield2 ELSEELSE '''' ENDEND asas AddrLine2 AddrLine2,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider City City,, StateState,,
Zip
Zip FROMFROM dbo dbo..EmployeeDirectiory atrEmployeeDirectiory atr
Thank you in Advance
Asita

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

asita - Tuesday, April 17, 2018 9:57 AM
Hello Good Morning,
How to optimize this code please advise....
SELECT CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2))))ELSEELSE '''' ENDEND asas AddFIELD2 AddFIELD2,, --- Check if any of 3 fields has "C/O" in it otherwise empty--- Check if any of 3 fields has "C/O" in it otherwise empty CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' ENDEND asas AddFIELD3 AddFIELD3,, --- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty--- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty LTRIMLTRIM((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'C/O''C/O' ,,''''),),'c/o''c/o',,''''),),'C/o''C/o',,''''),),'c/O''c/O',,'''')) )) asas WithoutCOAddress1 WithoutCOAddress1,, --- exclude C/O from the fields--- exclude C/O from the fields REPLACEREPLACE((LTRIMLTRIM(( REPLACEREPLACE((REPLACEREPLACE((REPLACEREPLACE ((REPLACEREPLACE(( REPLACEREPLACE(( REPLACEREPLACE(( CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'ATTN''ATTN',,''''),),'attn''attn',,''''),),'Attn''Attn',,''''),),'ATT:''ATT:',,''''),), 'Attention''Attention',,''''),),':'':',,'''')),)),';'';',,'''')) asas WithoutAttnAddress2 WithoutAttnAddress2,, --- exclude ATTN from the fields--- exclude ATTN from the fields CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL THENTHEN addressfield1 addressfield1 WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' THENTHEN ((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((ee..OvrFlowNmOvrFlowNm)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' ANDAND LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) ISIS NOTNOT NULLNULL ANDAND LENLEN((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))))) >> 0 0 THENTHEN addressfield3_O addressfield3_O ELSEELSE ISNULLISNULL((addressfield2addressfield2,,'''')) endend )) ELSEELSE '''' ENDEND asas AddrLine1 AddrLine1,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL ANDAND addressfield2 addressfield2 ISIS NOTNOT NULLNULL THENTHEN addressfield2 addressfield2 ELSEELSE '''' ENDEND asas AddrLine2 AddrLine2,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider City City,, StateState,,
Zip
Zip FROMFROM dbo dbo..EmployeeDirectiory atrEmployeeDirectiory atr
Thank you in Advance
Asita

You are kidding right? Look at the trainwreck you posted. But honestly it looks like just a select from a single table with no where clause so there isn't likely going to be much of anything you can do to "optimize" this.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Are you working with a case sensitive database? If not, you can reduce the number of functions used. You won't get a great improvement because you're reading the whole table and that's probably taking most of the time.
Here's a modified version of your query without most problems and with examples on the point I made.
SELECT CASE WHEN LTRIM(addressfield1) LIKE 'C/O%' THEN LTRIM(addressfield1) WHEN LTRIM(addressfield3_O) LIKE 'C/O%' THEN LTRIM(addressfield3_O) WHEN LTRIM(addressfield2) LIKE 'C/O%' THEN LTRIM(addressfield2) ELSE '' END as AddFIELD2, --- Check if any of 3 fields has "C/O" in it otherwise empty CASE WHEN LTRIM(addressfield3_O) LIKE 'ATT[:N]%' OR LTRIM(addressfield3_O)LIKE 'ATTENTION%' THEN LTRIM(addressfield3_O) WHEN LTRIM(addressfield1) LIKE 'ATT[:N]%' OR LTRIM(addressfield1)LIKE 'ATTENTION%' THEN LTRIM(addressfield1) WHEN LTRIM(addressfield2) LIKE 'ATT[:N]%' OR LTRIM(addressfield2)LIKE 'ATTENTION%' THEN LTRIM(addressfield2) ELSE '' END as AddFIELD3, --- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty LTRIM(REPLACE(CASE WHEN LTRIM(addressfield1) LIKE 'C/O%' THEN LTRIM(addressfield1) WHEN LTRIM(addressfield3_O) LIKE 'C/O%' THEN LTRIM(addressfield3_O) WHEN LTRIM(addressfield2) LIKE 'C/O%' THEN LTRIM(addressfield2) ELSE '' END,'C/O','')) as WithoutCOAddress1, --- exclude C/O from the fields LTRIM( REPLACE(REPLACE(REPLACE (REPLACE( REPLACE( CASE WHEN LTRIM(addressfield3_O) LIKE 'ATT[:N]%' OR LTRIM(addressfield3_O) LIKE 'ATTENTION%' THEN LTRIM(addressfield3_O) WHEN LTRIM(addressfield1) LIKE 'ATT[:N]%' OR LTRIM(addressfield1) LIKE 'ATTENTION%' THEN LTRIM(addressfield1) WHEN LTRIM(addressfield2) LIKE 'ATT[:N]%' OR LTRIM(addressfield2) LIKE 'ATTENTION%' THEN LTRIM(addressfield2) ELSE '' END, 'ATTN',''),'ATT:',''), 'Attention',''),':',''),';','')) as WithoutAttnAddress2, --- exclude ATTN from the fields CASE WHEN UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,3)) <> 'C/O' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,4)) <> 'ATTN' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,4)) <> 'ATT:' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,9)) <> 'ATTENTION' AND addressfield1 IS NOT NULL THEN addressfield1 WHEN UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,3)) <> 'C/O' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,4)) <> 'ATTN' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,4)) <> 'ATT:' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,9)) <> 'ATTENTION' THEN CASE WHEN UPPER(SUBSTRING(LTRIM(RTRIM(addressfield3_O)),1,3)) <> 'C/O' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield3_O)),1,4)) <> 'ATT:' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield3_O)),1,4)) <> 'ATTN' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield3_O)),1,9)) <> 'ATTENTION' AND addressfield3_O IS NOT NULL AND LEN(addressfield3_O) > 0 THEN addressfield3_O ELSE ISNULL(addressfield2,'') END ELSE '' END AS AddrLine1, --- if no Attn and c/o then conside CASE WHEN UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,3)) <> 'C/O' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,4)) <> 'ATTN' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,9)) <> 'ATTENTION' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,3)) <> 'C/O' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,4)) <> 'ATTN' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,9)) <> 'ATTENTION' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield1)),1,4)) <> 'ATT:' AND UPPER(SUBSTRING(LTRIM(RTRIM(addressfield2)),1,4)) <> 'ATT:' AND addressfield1 IS NOT NULL AND addressfield2 IS NOT NULL THEN addressfield2 ELSE '' END as AddrLine2, --- if no Attn and c/o then consider City, State, Zip FROM dbo.EmployeeDirectiory atr

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

asita - Tuesday, April 17, 2018 9:57 AM
Hello Good Morning,
How to optimize this code please advise....
SELECT CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O))))WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2))))ELSEELSE '''' ENDEND asas AddFIELD2 AddFIELD2,, --- Check if any of 3 fields has "C/O" in it otherwise empty--- Check if any of 3 fields has "C/O" in it otherwise empty CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' ENDEND asas AddFIELD3 AddFIELD3,, --- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty--- Check if any of 3 fields has "ATTN" or "Attention" or "Att:" in it otherwise empty LTRIMLTRIM((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE ((REPLACEREPLACE((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) == 'C/O''C/O' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'C/O''C/O' ,,''''),),'c/o''c/o',,''''),),'C/o''C/o',,''''),),'c/O''c/O',,'''')) )) asas WithoutCOAddress1 WithoutCOAddress1,, --- exclude C/O from the fields--- exclude C/O from the fields REPLACEREPLACE((LTRIMLTRIM(( REPLACEREPLACE((REPLACEREPLACE((REPLACEREPLACE ((REPLACEREPLACE(( REPLACEREPLACE(( REPLACEREPLACE(( CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)))) WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATTN''ATTN' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) == 'ATT:''ATT:' OROR UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) == 'ATTENTION''ATTENTION' THENTHEN LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)))) ELSEELSE '''' endend ,,'ATTN''ATTN',,''''),),'attn''attn',,''''),),'Attn''Attn',,''''),),'ATT:''ATT:',,''''),), 'Attention''Attention',,''''),),':'':',,'''')),)),';'';',,'''')) asas WithoutAttnAddress2 WithoutAttnAddress2,, --- exclude ATTN from the fields--- exclude ATTN from the fields CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL THENTHEN addressfield1 addressfield1 WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' THENTHEN ((CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((ee..OvrFlowNmOvrFlowNm)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' ANDAND LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))) ISIS NOTNOT NULLNULL ANDAND LENLEN((LTRIMLTRIM((RTRIMRTRIM((addressfield3_Oaddressfield3_O)))))) >> 0 0 THENTHEN addressfield3_O addressfield3_O ELSEELSE ISNULLISNULL((addressfield2addressfield2,,'''')) endend )) ELSEELSE '''' ENDEND asas AddrLine1 AddrLine1,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider CASECASE WHENWHEN UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,33)))) <><> 'C/O''C/O' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATTN''ATTN' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,99)))) <><> 'ATTENTION''ATTENTION' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield1addressfield1)),)),11,,44)))) <><> 'ATT:''ATT:' andand UPPERUPPER((SUBSTRINGSUBSTRING((LTRIMLTRIM((RTRIMRTRIM((addressfield2addressfield2)),)),11,,44)))) <><> 'ATT:''ATT:' andand addressfield1 addressfield1 ISIS NOTNOT NULLNULL ANDAND addressfield2 addressfield2 ISIS NOTNOT NULLNULL THENTHEN addressfield2 addressfield2 ELSEELSE '''' ENDEND asas AddrLine2 AddrLine2,, --- if no Attn and c/o then consider--- if no Attn and c/o then consider City City,, StateState,,
Zip
Zip FROMFROM dbo dbo..EmployeeDirectiory atrEmployeeDirectiory atr
Thank you in Advance
Asita

A WHERE clause? Maybe on a column that has an index on it?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

To All thank you much, it is straight table so I am looking to make it small statement rather than this big query for what I am doing now

please forgive me if I sound not right in my question initially

Thank you Luis

It helps me Like Att[:n] Thank you much