String comparisons - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

String comparisons

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-05 : 18:03:30

I am going to be preforming a lot od string comparisons and need to understand how SQL does comparisons.

A simple example is two tables in which name is 'FRANK'. In the first SELECT the names match . In the second they don't.

SELECT * FROM ZMain A
inner join ZNew B
ON left(A.Address,3) = B.Address

SELECT * FROM ZMain A
inner join ZNew B
ON left(A.Name,3) = B.Name

I wanted a character for character match left to right. if the shorter string matches i want a match. How do I accomplish this?

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-05 : 21:04:13

Did you know your joins can have more than one criteria?
[CODE]
SELECT e.surname
, e.forename
, b.benefit_code
FROM employees e
INNER
JOIN benefits b
ON e.employeeID = b.employeeID
[AND/OR ...]
WHERE e.forename = 'george'
[/CODE]

George
<3Engaged!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-01-05 : 21:15:19

You need to use two left outer joins to the same table, one on each condition. see:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

Rule of thumb: you should rarely, if ever, use OR in a join expression.

- Jeff
http://weblogs.sqlteam.com/JeffS

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-06 : 03:35:34

Sorry I made a mistake in my original post. The first query should have been as follows.

SELECT * FROM ZMain A
inner join ZNew B
ON A.name = B.name

SELECT * FROM ZMain A
inner join ZNew B
ON left(A.Name,3) = B.Name

I need to understand why these two SELECTs do not yeld the same result? If Name = 'FRANK' in both tables, why does the first SELECT find a match but the second does not?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-06 : 03:42:32

using left(A.Name, 3) will give you first 3 chars of the data in Name column. For Name = 'FRANK', it will return you 'FRA'.

How can 'FRA' equal to 'FRANK' ?

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-01-06 : 03:46:35

this should work, I didn't verify the syntax though. Basically your saying if table b matches the first 3 letters of table a then join to it.


SELECT * FROM ZMain A
inner join ZNew B
on b.name like left(A.Name,3) + '%'

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-06 : 03:46:42

I wanted a character for character match left to right. if the shorter string matches i want a match. How do I accomplish this? In FoxPro this is an ANSI setting.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2008-01-06 : 04:03:03

Ahhhh use the LIKE operator and wildcards not =. Thank you for the example.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources