InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 11:39:46 GMT20http://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxI like to advise people to avoid self-JOINs whenever possible because I've always found them to be performance dogs across large row sets. Woof!Not sure this is faster than the EXISTS or IN solutions proposed earlier, but it will almost certainly be swifter than a self JOIN.[code="sql"]DECLARE @T TABLE (ProductID VARCHAR(50))INSERT INTO @TSELECT '003223' UNION ALL SELECT '003225' UNION ALL SELECT '003227'UNION ALL SELECT '003227A' UNION ALL SELECT '003236' UNION ALL SELECT '003236A'UNION ALL SELECT '003241' UNION ALL SELECT '003273' UNION ALL SELECT '003273A'SELECT ProductIDFROM ( SELECT ProductID, m=MAX(n) OVER (PARTITION BY LEFT(ProductID, 6)) FROM ( SELECT ProductID ,n=ROW_NUMBER() OVER (PARTITION BY LEFT(ProductID, 6) ORDER BY (SELECT NULL)) FROM @T) a) bWHERE m > 1[/code]Anyway, that is another alternative you can consider.Wed, 12 Dec 2012 17:47:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxThanks for your help everyone. Did some further work last night and came up with a solution for my needs.SELECT a.ur_number, b.ur_number, c.name_last, c.name_firstFROM SYS_CLI_Client aINNER JOIN SYS_CLI_Client b ON (SUBSTRING(a.ur_number, 1, LEN(a.ur_number) - 1)=b.ur_number)INNER JOIN SYS_GEN_Person c on (b.person_id = c.id) WHERE a.ur_number LIKE ('%[A-Z]')Wed, 12 Dec 2012 14:50:44 GMTterryphonehttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxLike wise, assuming you are looking at the first 6 characters.SELECT ID FROM (SELECT SUBSTRING(ID,1,6) ID,COUNT(1)x FROM #TGROUP BY SUBSTRING(ID,1,6))D WHERE D.x>1Tue, 11 Dec 2012 21:42:26 GMTpeacesellshttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxSorry - I forgot the crucial "AND t2.ID <> t1.ID". I've corrected my original post.Tue, 11 Dec 2012 20:49:19 GMTdoug.baker-706021http://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxHey Terry unless I understood wrong I am not sure that Dougs solution actually does what you want. Sorry Doug, not sure you tested your code or perhaps you mistyped something.The code I gave gives 6 rows, Doug yours gave 9.NOTE: I'm new to this so when I posted my T-SQL it still converted the > (greater than sign) to the escaped characters, ah well .Tue, 11 Dec 2012 20:37:24 GMTShineBoyhttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxThanks Doug, But it does not seem to be filtering out the id's that are single entries.Tue, 11 Dec 2012 20:36:10 GMTterryphonehttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxHi terryphone,If the data is reliably as you listed (the data is alphanumeric and the "like" IDs are always 6 numeric digits with or without a letter appended), you should be able to get your list from the following query:[code="sql"]SELECT ID --I'm assuming the column name is ID, substitute the true column nameFROM TableName t1 --I'm giving your table this generic table name, substitute the true table nameWHERE EXISTS ( SELECT * FROM TableName t2 --substitute the true table name here as well WHERE LEFT(t2.ID,6) = LEFT(t1.ID,6) --substitute the true column name here as well AND t2.ID <> t1.ID )ORDER BY ID[/code]Hope that helps.--DougTue, 11 Dec 2012 20:24:53 GMTdoug.baker-706021http://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxAssumed that field name is ID and that it is the first 6 characters is the portion you are looking atI loaded it all into a temp table and used:[code="sql"]Select ID from #Temp where Left(id,6) IN (Select left(id,6) from #Temp group by left(id,6) having count(*) > 1)[/code]Tue, 11 Dec 2012 20:23:02 GMTShineBoyhttp://www.sqlservercentral.com/Forums/Topic1395396-392-1.aspxHi. I have a table with figures below.003223003225003227003227A003236003236A003241003273003273AI need to do a search to find all items where the id is like other id's and exclude all others.So i need to list 003227003227A003236003236A003273003273ATue, 11 Dec 2012 19:46:12 GMTterryphone