Match the SQL pattern for the first 5 characters

Question

I am thinking of an SQL query that returns me all the records from a column whose first 5 characters match. Any ideas? I am thinking of entries where ANY first 5 characters match, not specific. For instance.

HelloA HelloB ThereC ThereD Something 

will return the first four records:

 HelloA HelloB ThereC ThereD 

EDIT: I am using SQL92, so I can not use the left command!

Answer 1

Try the following:

 SELECT * FROM YourTable WHERE LEFT(stringColumn, 5) IN ( SELECT LEFT(stringColumn, 5) FROM YOURTABLE GROUP BY LEFT(stringColumn, 5) HAVING COUNT(*) > 1 ) 

SQLFIDDLE DEMO

This selects the first 5 characters, groups them and returns only those that occur more than once.

Or with a substring:

 SELECT * FROM YourTable WHERE substring(stringColumn,1,5) IN ( SELECT substring(stringColumn,1,5) FROM YOURTABLE GROUP BY substring(stringColumn,1,5) HAVING COUNT(*) > 1) ; 

SQLFIDDLE DEMO

Answer 2

Sounds easy enough ...

In SQL Server, it will be something like rows

 where Left(ColumnName,5) = '12345' 

Answer 3

Try

 Select * From tbl t1 Where exists ( Select 1 From tbl t2 Where left(t1.str, 5) = left(t2.str) Group by left(t2.str, 5) Having count(1) > 1 ) 

Answer 4

You did not specify your DBMS. If it supports Windowed Aggregate functions, this is:

 select * from ( select tab.*, count(*) over (partition by substring(col from 1 for 5) as cnt from tab ) as dt where cnt > 1 

Answer 5

You want to work with CTE .

Something like that:

 with CountriesCTE(Id, Name) as ( select Id, Name from Countries ) select distinct Countries.Name from CountriesCTE, Countries where left(CountriesCTE.Name,5) = left(Countries.Name,5) and CountriesCTE.Id <> Countries.Id