Help with SQL - 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)

Help with SQL

Author

Topic

s.wiley7
Starting Member

4 Posts

Posted - 2009-02-23 : 14:51:03

I need to make sure the states are insynch between the two tables. First table lists the state, and the other table lists the city and state with a comma as the delimiter.

Any thoughts?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 14:52:54

And what did you try?

s.wiley7
Starting Member

4 Posts

Posted - 2009-02-23 : 15:02:49

SELECT A.STATE_CD, C.CITY_STATE_CD
FROM temp1 a,
temp2 c
WHERE A.STATE_CD IS NOT NULL AND C.CITY_STATE_CD IS NOT NULL
and C. C.CITY_STATE_CD like '%,%'
and STATE_CD = C. C.CITY_STATE_CD <---not sure how to set up to parse

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 15:07:20

Maybe:

Select * from Tbl1 t
Left outer join Tb2 m
On t.State_CD = Right(C.City_state_cd,2)
Where Right(C.City_state_cd,2) Is NULL

s.wiley7
Starting Member

4 Posts

Posted - 2009-02-23 : 16:10:23

That didn't work.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 16:28:35

It should work:

Anyway try this:

Select * from Tbl1 t
Left outer join Tb2 m
On t.State_CD = Substring(C.City_state_cd,Charindex(',',C.City_state_cd)+1,2)
Where Substring(C.City_state_cd,Charindex(',',C.City_state_cd)+1,2) Is NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources