Comparing a to a substring

Question

Post reply

Comparing a to a substring

SQLTestUser

Hall of Fame

Points: 3997
More actions
September 11, 2013 at 5:06 pm

#298647

I have a filed UserName as for
Table A
"Z109032, My name" and a query as
SELECT LEFT(UserName, 7) as CAR FROM car10 to get Z109032 which needs to be compared to another table that has Z109032. The other table has the field username with just Z109032 and the first and the last name in different fields as
Table B
ID first last
Z109032 My Name
is there any way we can compare these fields in the two tables A and B so that i can find the user on the other table based on the substring of the first table.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Amit Raut SSCrazy Points: 2613 More actions · Answer 1

SELECT * FROM Table A

INNER JOIN Table B

WHERE LEFT(A.UserName, 7) = B.ID -- RELEVANT FIELD FROM TABLE B

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Amit Raut (9/11/2013)
SELECT * FROM Table A
INNER JOIN Table B
WHERE LEFT(A.UserName, 7) = B.ID -- RELEVANT FIELD FROM TABLE B

Its not a good practice to use functions like LEFT, RIGHT in where as it will leads to a table scan and it effects performance.

You can use CTE for it like this:

WITH CTE AS

(

SELECT LEFT(A.UsrName,7) as UserName FROM Table A

)

SELECT *

FROM CTE C

INNER JOIN TABLEB ON B.ID = C.userName

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

kapil_kk (9/11/2013)
Amit Raut (9/11/2013)
SELECT * FROM Table A
INNER JOIN Table B
WHERE LEFT(A.UserName, 7) = B.ID -- RELEVANT FIELD FROM TABLE B
Its not a good practice to use functions like LEFT, RIGHT in where as it will leads to a table scan and it effects performance.
You can use CTE for it like this:
WITH CTE AS
(
SELECT LEFT(A.UsrName,7) as UserName FROM Table A
)
SELECT *
FROM CTE C
INNER JOIN TABLEB ON B.ID = C.userName

What difference will the CTE make, Kapil?

The query should be

SELECT *

FROM Table A

INNER JOIN Table B

ON LEFT(A.UserName, 7) = B.ID

^{“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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

What difference will the CTE make, Kapil?
The query should be
SELECT *
FROM Table A
INNER JOIN Table B
ON LEFT(A.UserName, 7) = B.ID

I was jjust trying to eliminate the LEFT from the WHERE clause as Amit did.

This can can also be done in the manner as you did.

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

The CTE is nothing more than a macro, it will be substituted into the query and result in LEFT() in the join. There's no gain.

^{“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

kapil_kk SSC-Insane Points: 21316 More actions · Answer 6

ChrisM@Work (9/12/2013)
The CTE is nothing more than a macro, it will be substituted into the query and result in LEFT() in the join. There's no gain.

Okay..

Thanks for the suggestion Chris 😀

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

SQLTestUser Hall of Fame Points: 3997 More actions · Answer 7

SQLTestUser

Hall of Fame

Points: 3997

September 12, 2013 at 10:05 am

#1649797

Thanks Chris the query worked

thanks again

Amit Raut SSCrazy Points: 2613 More actions · Answer 8

ChrisM@Work (9/12/2013)
kapil_kk (9/11/2013)
Amit Raut (9/11/2013)
SELECT * FROM Table A
INNER JOIN Table B
WHERE LEFT(A.UserName, 7) = B.ID -- RELEVANT FIELD FROM TABLE B
Its not a good practice to use functions like LEFT, RIGHT in where as it will leads to a table scan and it effects performance.
You can use CTE for it like this:
WITH CTE AS
(
SELECT LEFT(A.UsrName,7) as UserName FROM Table A
)
SELECT *
FROM CTE C
INNER JOIN TABLEB ON B.ID = C.userName
What difference will the CTE make, Kapil?
The query should be
SELECT *
FROM Table A
INNER JOIN Table B
ON LEFT(A.UserName, 7) = B.ID

Oops... I used WHERE instead of ON. My mistake 😉