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