select doubles from the first 2 characters

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

General SQL Server Forums

New to SQL Server Programming

select doubles from the first 2 characters

Author

Topic

mungojerry312002
Starting Member

4 Posts

Posted - 2008-06-30 : 07:27:40

I'm a little lost and haven't been able to find any direction in the FAQ or older posts.

I have 2 columns Zip Code and City Name. I would like to run a search to select only the rows which have the same city name and the first 2 characters of the zip code are the same. For exapmle I would like the select to return only the green rows.

ZIP CODE CITY NAME
67227 FRANKENTHAL
60598 FRANKFURT AM MAIN
60528 FRANKFURT AM MAIN
60549 FRANKFURT AM MAIN
69443 FRANKFURT AM MAIN

Thanks in advance for your help, it is greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 07:43:36

[code]SELECT t1.*
FROM YourTable t1
INNER JOIN (SELECT LEFT([ZIP CODE],2) as Code,[CITY NAME]
FROM YourTable
GROUP BY LEFT([ZIP CODE],2),[CITY NAME]
HAVING COUNT(*) >1) t2
ON t2.Code=LEFT(t1.[ZIP CODE],2)
AND t2.[CITY NAME] =t1.[CITY NAME] [/code]

mungojerry312002
Starting Member

4 Posts

Posted - 2008-06-30 : 08:17:23

Thanks visakh16,
I adapted your script but was unable to get it to work... it kept on coming up with the Error Message "ORA-00936:: missing expression" and bring the curser to where I put the 'XXX' below (between the Left and Zip Code). Any ideas? Thanks again for your help.

SELECT t1.*
FROM s_temp_vid_3 t1
INNER JOIN (SELECT LEFT(XXX[field2],2) as Code,[field3]
FROM s_temp_vid_3
GROUP BY LEFT([field2],2),[field3]
HAVING COUNT(*) >1) t2
ON t2.Code=LEFT(t1.[field2],2)
AND t2.[field3] =t1.[field3]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-06-30 : 08:29:16

quote:
Error Message "ORA-00936:: missing expression"

That's because you are using the wrong database server. You should be using MS SQL Server not Oracle

Oh . . in case you have not notice, this is a Microsoft SQL Server forum. The solution we posted here is for MS SQL Server

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources