SQL Server Performance

Using CHARINDEX to extract when exists but not when it does not.

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by tthomasol, Apr 8, 2008.

  1. tthomasol New Member

    I am attempting to pull some data from an old invoice system where the Address Information is stored in the same field with a char13 and char10 to indicate CR and LF. The new Data structure has an AddressLine1 and an AddressLine2. I'm attempting to extract the address lines and put them in the proper associated fields. My problem is that they do not all contain CR LF values. So while my statement below works for addresses containing these fields it ends up returning nothing for addresses that don't have the CR and LF values. I wanted to do all of this in one query but I'm open for suggestions. Does SQL have an equivalent to an In Line IF? I thought of using an IF statement but I think that's going to require a stored procedure and I don't know how to tie that into my query if I use a SP. Any advice would be appreciated.
    SELECT LEFT(ShipperAddr1,CHARINDEX(char(13),ShipperAddr1,0)) AS ShipperAddr1,
    RIGHT(ShipperAddr1,CHARINDEX(char(10),ShipperAddr1,0)) AS ShipperAddr2 FROM
    v_Last5YearsInvoices
  2. ranjitjain New Member

    Hi You can use inline case logic something like this :
    declare
    @t1 varchar(100)select
    @t1='XYZ'+char(13)+'MUMBAI'SELECT
    LEFT(@t1,case
    WHEN CHARINDEX(char(13),@t1,0)=0 then 4 else CHARINDEX(char(13),@t1,0) END)AS ShipperAddr1, RIGHT(
    @t1,case WHEN CHARINDEX(char(10),@t1,0)=0 then 7 else CHARINDEX(char(10),@t1,0) END) AS ShipperAddr2
  3. tthomasol New Member

    Thanks for your help Ranjit Jain,[:D]
    Using your advice, I got it to work the way I wanted to. Here is my final code.LEFT
    (tblAccounts_1.AcctAddress,case WHEN CHARINDEX(char(13) + char(10),tblAccounts_1.AcctAddress,0)=0 then LEN(tblAccounts_1.AcctAddress) else CHARINDEX(char(13),tblAccounts_1.AcctAddress,0) - 1 END) AS ShipperAddr1, RIGHT(
    tblAccounts_1.AcctAddress,case WHEN CHARINDEX(char(13) + char(10),tblAccounts_1.AcctAddress,0)=0 then NULL else LEN(tblAccounts_1.AcctAddress) - 1 - CHARINDEX(char(13) + char(10),tblAccounts_1.AcctAddress) END) AS ShipperAddr2,

Share This Page