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!
Try this :
SELECT *
FROM YourTable
WHERE LEFT(stringColumn, 5) IN (
SELECT LEFT(stringColumn, 5)
FROM YOURTABLE
GROUP BY LEFT(stringColumn, 5)
HAVING COUNT(*) > 1
)
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)
;