Pattern matching SQL on first 5 characters

Question

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!

Use `substring` for the left command... [link](http://www.postgresql.jp/document/pg702doc/user/x2732.htm) — danielQ, Nov 06 '13 at 20:10

Filipe Silva · Accepted Answer · 2013-11-06T20:18:15.817

12

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

edited Nov 06 '13 at 20:18

answered Nov 06 '13 at 19:26

Filipe Silva

21,189
5
53
68

please see my edited question. I meant any first 5 characters, not specific ones, so probably cannot use like. – MJP Nov 06 '13 at 19:30
@MJP. Updated my answer. see if it is what you want. And usually it is a good idea to put the tag of the flavour of sql you are using in the question. – Filipe Silva Nov 06 '13 at 19:39
done! I think in my version of DBMS, I cannot use the left command! – MJP Nov 06 '13 at 20:00

score 8 · Answer 2 · answered Nov 06 '13 at 19:26

8

Sounds easy enough...

In SQL Server this would be something along the lines of

where Left(ColumnName,5) = '12345'

answered Nov 06 '13 at 19:26

Tim

4,051
10
36
60

score 0 · Answer 3 · answered Nov 06 '13 at 19:41

0

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
 )

answered Nov 06 '13 at 19:41

T I

9,785
4
29
51

score 0 · Answer 4 · answered Nov 06 '13 at 19:48

0

You didn't specify your DBMS. If it supports Windowed Aggregate functions it's:

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

answered Nov 06 '13 at 19:48

dnoeth

59,503
4
39
56

score 0 · Answer 5 · edited May 13 '17 at 00:16

0

You want to work with a CTE approach.

Something like this:

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

edited May 13 '17 at 00:16

Nathan Tuggy

2,237
27
30
38

answered Nov 06 '13 at 19:50

danielQ

2,016
1
15
19

Pattern matching SQL on first 5 characters

5 Answers5