CODEWITHSUNDEEP
postgresql
ssh
ssh

Reputation: 25

Find rows where string contains certain character at specific place

I have a field in my database, that contains 10 characters: Fx: 1234567891

I want to look for the rows where the field has eg. the numbers 8 and 9 in places 5 and 6

So for example, if the rows are

a) 1234567891
b) 1234897891
c) 1234877891

I only want b) returned in my select. The type of the field is string/character varying.

I have tried using:

where field like '%89%'

but that won't work, because I need it to be 89 at a specific place in the string.

Upvotes: 2

Views: 1352

Answers (2)

D. Worthy
D. Worthy

Reputation: 1

You should be able to evaluate the single character using the underscore(_) character. So you should be able to use it as follows.

where field like '____89%'

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 248075

The fastest solution would be

WHERE substr(field, 8, 2) = '89'

If the positions are not adjacent, you end up with two conditions joined with AND.

Upvotes: 2

Related Questions