Post reply

INNER JOIN with a String Challenge

  • July 14, 2011 at 6:46 pm

    #244056

    Hi Helpers,

    I have a join challenge....

    I have a table with customers and another table with addresses as show below:

    CREATE TABLE #MySample

    (

    PK int NOT NULL,

    ID varchar(15)not null,

    LastName varchar(35) null,

    FirstName varchar(35) null

    )

    INSERT INTO #MySample VALUES (1, '1234567891011','Smith','Joan')

    INSERT INTO #MySample VALUES (2, 'E505467891001','Stell','Juan')

    INSERT INTO #MySample VALUES (3, '125L456788095','Castill','El')

    INSERT INTO #MySample VALUES (4, '1234567898943','Calan','Katin')

    INSERT INTO #MySample VALUES (5, 'ET567754623','Testing','Test')

    SELECT * from #MySample

    ;

    CREATE TABLE #MyAddress

    (

    PK int NOT NULL,

    AddressCode varchar(18) NOT NULL,

    MyaddressOne varchar(35) null,

    MyaddressTwo varchar(35) null,

    MyCity varchar(20) null,

    MyST varchar (2) null,

    MyZip varchar(10) null

    )

    INSERT INTO #MyAddress VALUES(1,'A B 123456789','123 South River','Apt 1','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(2,'A A 548954512','1384 North River','Apt 30','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(3,'Z Z 123456789','38450 River Rd','Apt 10','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(4,'A A 548954512','1384 North River','Apt 30','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(5,'A B E50546789','123 River Drive','Apt 1','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(6,'A B 125L45678','35 This River','Apt 50','Miami','FL','33133')

    SELECT * FROM #MyAddress

    DROP TABLE #MySample

    DROP TABLE #MyAddress

    I must join ID with address code where code start with 'A B ' + 9 digits.

    There are 3 spaces between A and B, and there is 1 space after B.

    Thank you!

  • July 14, 2011 at 8:13 pm

    #1353148

    DO you need to keep the spaces?

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 14, 2011 at 8:27 pm

    #1353153

    Yes, I would not be able to modify the data on the source #MyAddress table. 🙁

    However, I would like to implement a permanenet solution so I can always use it to get the address.

  • July 14, 2011 at 8:38 pm

    #1353154

    JohnDBA

    I must join ID with address code where code start with 'A B ' + 9 digits.

    There are 3 spaces between A and B, and there is 1 space after B.

    Thank you!

    Maybe its me but the data supplied does not match ID is mainly a number and address code has the ab space issue...Are you wanting to join the first 9 digits of the ID with the 9 digits at the end of addresscode?

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 14, 2011 at 9:05 pm

    #1353161

    Since sargability and index seeking are pretty much out the window, this should work for your join:

    REVERSE( RIGHT( REVERSE( AddressCode), 9)) = ID

    EDIT: Misplaced a parentheses.

    Ignore this, massive blonde moment.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 14, 2011 at 9:11 pm

    #1353163

    Maybe its me but the data supplied does not match ID is mainly a number and address code has the ab space issue...Are you wanting to join the first 9 digits of the ID with the 9 digits at the end of addresscode?

    Yes

  • July 14, 2011 at 9:57 pm

    #1353172

    Craig Farrell (7/14/2011)

    Since sargability and index seeking are pretty much out the window, this should work for your join:

    REVERSE( RIGHT( REVERSE( AddressCode), 9)) = ID

    EDIT: Misplaced a parentheses.

    I have to ask, Craig... REVERSE is fairly expensive performance-wise. Why any REVERSE's to begin with?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 14, 2011 at 10:04 pm

    #1353173

    Jeff Moden (7/14/2011)

    Craig Farrell (7/14/2011)

    Since sargability and index seeking are pretty much out the window, this should work for your join:

    REVERSE( RIGHT( REVERSE( AddressCode), 9)) = ID

    EDIT: Misplaced a parentheses.

    I have to ask, Craig... REVERSE is fairly expensive performance-wise. Why any REVERSE's to begin with?

    EDIT: FACEPALM :blush:

    I'm going to be Over THERE now... Sorry.

    That should be REVERSE( LEFT( REVERSE(... or RIGHT ( AddressCode, 9).

    I had doing a charindex to the space in my head at first and then realized it was unnecessary... and didn't revert my code.

    Thank you Jeff.

    *wanders off in search of a good tome and the words to the self-head-thumping mantra from Monty Python...*

    Sorry about that JohnDBA, you posted a perfectly good test bed and I failed to take advantage of it before I posted. I know better. My apologies.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • July 14, 2011 at 11:50 pm

    #1353198

    Craig Farrell (7/14/2011)

    EDIT: FACEPALM :blush:

    Heh... I'm not sure where you live but I'm pretty sure I heard that one way up here in Michigan. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 15, 2011 at 6:02 am

    #1353354

    JohnDBA

    I think this is what you wanted:

    SELECT * FROM #MyAddress a join #mysample s on

    Right(replace(a.AddressCode,' ',''),9) = LEFT(s.ID,9)

    where replace(a.AddressCode,' ','') like ('AB%')

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 15, 2011 at 6:32 am

    #1353387

    I have to ask, Craig... REVERSE is fairly expensive performance-wise. Why any REVERSE's to begin with?

    Jeff, better suggestions? Thank you!

  • July 15, 2011 at 7:12 am

    #1353417

    John

    did you try my suggestion no reverse at all:

    SELECT * FROM #MyAddress a join #mysample s on

    Right(replace(a.AddressCode,' ',''),9) = LEFT(s.ID,9)

    where replace(a.AddressCode,' ','') like ('AB%')

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 15, 2011 at 7:37 am

    #1353429

    I did, but per example, it should only return 3 records, and I get 4.

  • July 15, 2011 at 7:48 am

    #1353433

    JohnDBA

    I must join ID with address code where code start with 'A B ' + 9 digits.

    There are 3 spaces between A and B, and there is 1 space after B.

    Thank you!

    The info that you provided to the criteria would return 4 is there something that you over looked to tell us?

    INSERT INTO #MySample VALUES (1, '

    123456789 --match value 1

    1011','Smith','Joan')

    INSERT INTO #MySample VALUES (2, '

    E50546789 --match value 2

    1001','Stell','Juan')

    INSERT INTO #MySample VALUES (3, '

    125L45678 --match value 3

    8095','Castill','El')

    INSERT INTO #MySample VALUES (4, '

    123456789 --match value 4

    8943','Calan','Katin')

    INSERT INTO #MyAddress VALUES(1,'

    A B

    123456789 --match value 1 and 4

    ','123 South River','Apt 1','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(5,'

    A B

    E50546789 --match value 2

    ','123 River Drive','Apt 1','Miami','FL','33133')

    INSERT INTO #MyAddress VALUES(6,'

    A B

    125L45678 --match value 3

    ','35 This River','Apt 50','Miami','FL','33133')

    If you want to combine 1 and 4 Values you will need to join the address table to itself combining the the addresses in a pivot or something like that. The other thing is that would put 2 people living at the same address, but the zz one also has the 123456789 value. So I am not complete sure that this will work with what is provided.

    :cool:

    "There are no problems! Only solutions that have yet to be discovered!"

  • July 15, 2011 at 7:58 am

    #1353440

    INSERT INTO #MySample VALUES (4, '

    123456789 --match value 4

    8943','Calan','Katin')

    This record does not match because in the #MyAddress table it is NO 'A B '

    This record is 'Z Z '

Viewing 15 posts - 1 through 15 (of 25 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy