Pattern matching SQL on first 5 characters

MJP picture MJP · Nov 6, 2013 · Viewed 35.6k times · Source

I'm thinking about a SQL query that returns me all entries from a column whose first 5 characters match. Any ideas? I'm thinking about entries where ANY first 5 characters match, not specific ones. E.g.

HelloA
HelloB
ThereC
ThereD
Something

would return the first four entries:

HelloA
HelloB
ThereC
ThereD

EDIT: I am using SQL92 so cannot use the left command!

Answer

Filipe Silva picture Filipe Silva · Nov 6, 2013

Try this :

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 by them and returns only the ones that happen more than once.

Or with 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