Search code examples
sqlreplacecharindex

SSMS - SQL - CHARINDEX at Carriage Return

In SQL I am trying to take in an address of varying length and separate it at a carriage return into two separate fields. I have the address successfully separated, but every so often (17 out of 300 times) the right side of the address comes out incorrect.

Adr1: RM320 VOTECH(Char(10))abc BUFORD AVE,

Adr2: FIELD ATHLETIC BLDG(Char(10))abc 15TH AVE SE,

There is no space before or after the carriage return.

select replace(RIGHT( REPLACE( 'RM320 VOTECH
abc BUFORD AVE,' , LEFT( 'RM320 VOTECH
abc BUFORD AVE,' , 
CHARINDEX( CHAR( 10 ) , 'RM320 VOTECH
abc BUFORD AVE,' )) , '' ) , CHARINDEX( CHAR( 10 ) , 'RM320 VOTECH
abc BUFORD AVE,' )),',','')

This returns: "bc BUFORD AVE" when it should return "abc buford ave"

select replace(RIGHT( REPLACE( 'FIELD ATHLETIC BLDG
abc 15TH AVE SE,' , LEFT( 'FIELD ATHLETIC BLDG
abc 15TH AVE SE,' , 
CHARINDEX( CHAR( 10 ) , 'FIELD ATHLETIC BLDG
abc 15TH AVE SE,' )) , '' ) , CHARINDEX( CHAR( 10 ) , 'FIELD ATHLETIC BLDG
abc 15TH AVE SE,' )),',','')

This returns: "abc 15TH AVE SE" correctly.

How do two identical selects return different results?

Solution

  • I don't think you need the replace( RIGHT( parts.

    If you use:

    select REPLACE( 'RM320 VOTECH@abc BUFORD AVE,' ,
      LEFT( 'RM320 VOTECH@abc BUFORD AVE,' , 
        CHARINDEX( '@' , 'RM320 VOTECH@abc BUFORD AVE,' )
       ) , '' );

result: 

abc BUFORD AVE,

    Note: I have replaced the CHAR(10) with @ for readability.