Searching a part of String in string Column

• chetanr.jain

  Hall of Fame

  Points: 3938

  More actions

  September 8, 2010 at 11:58 pm

  #240108

  Hi,

  I require a little help on string query.

  I have two table ( Table A and Table B) each having employee column with string values.

  I want to search that does part of Table A.employee column is having matching value in Table B.employee column

  Table A. Employee

  -----------------

  Steve Smith

  John Davis

  Table B. Employee

  -----------------

  Steve Craig

  Peter Thomas

  I want to search if Employee having name "Steve" from table A is having matching value from TableB. Employee.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 9, 2010 at 12:08 am

  #1218375

  chetanr.jain (9/8/2010)

  ---

  Hi,

  I require a little help on string query.

  I have two table ( Table A and Table B) each having employee column with string values.

  I want to search that does part of Table A.employee column is having matching value in Table B.employee column

  Table A. Employee

  -----------------

  Steve Smith

  John Davis

  Table B. Employee

  -----------------

  Steve Craig

  Peter Thomas

  I want to search if Employee having name "Steve" from table A is having matching value from TableB. Employee.

  Not sure what you are asking for here. Based on the above, would Steve Smith in Table A match Steve Craig in Table B?

• NickDBA

  Hall of Fame

  Points: 3792

  More actions

  September 9, 2010 at 5:34 am

  #1218519

  What if you have numerous employees with (for example) the first name of "steve" in both tables? Any join on part of a string (or substring) is going to bring you back some interesting results. Be worth double checking the requirements and business rules

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  September 9, 2010 at 5:44 am

  #1218525

  chetanr.jain (9/8/2010)

  ---

  Hi,

  I require a little help on string query.

  I have two table ( Table A and Table B) each having employee column with string values.

  I want to search that does part of Table A.employee column is having matching value in Table B.employee column

  Table A. Employee

  -----------------

  Steve Smith

  John Davis

  Table B. Employee

  -----------------

  Steve Craig

  Peter Thomas

  I want to search if Employee having name "Steve" from table A is having matching value from TableB. Employee.

  The solution to this is fairly trivial. The name string is resolved out as words in separate rows, enabling a common JOIN to match the two tables.

  But as others have pointed out, what on earth would you want to do this for?

  An explanation of what this operation is expected to achieve would be helpful. If you're really looking to detect matches on full name - e.g. "Craig, S." matching "Steve Craig" then it will be quite a lot more complex.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden