CODEWITHSUNDEEP
postgresqlsql-like
user1001738
user1001738

Reputation:

pattern matching using "LIKE" in query ,postgres

How to use "LIKE" to find rows that have for instance "R011","R012","R021","R022" in their string?I dont want "R01" nor "R02" to be selected.

SELECT 'Code'
FROM "public"."Test"
WHERE 'Code' LIKE 'R01%';

in a table named "Test" in column "Code" I have used Above query but the result was:

no rows found.

rows are like the following:

R(01-04,11)  Personnel professionnel des
R01  M้decins, dentistes et v้t้r.
R011 M้decins sp้cialistes
R022 Omnipraticiens et m้decins en m้decine

Upvotes: 0

Views: 1187

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656301

SELECT *
FROM   public."Test"
WHERE  "Code" LIKE 'R0__';

This requires 2 characters after 'R0'.

Your use of single quotes makes no sense. Start by reading about identifiers and key words in the manual. My advice: do not use mixed case identifiers at all.
Then proceed to information about pattern matching.

To be more specific you could use a regular expression like:

WHERE  "Code" ~ '^R0\d\d$';  -- allowing any 2 digits

or even:

WHERE  "Code" ~ '^R0[12]{2}$';  -- allowing only 1 and 2

Upvotes: 2

Benbob
Benbob

Reputation: 14234

You want to use the underscore instead of the percent character. Underscore _ matches a single character, where as percent % matches zero or more characters.

select 'R011' like 'R01_'
# true

select 'R01' like 'R01_'
# false

Or better yet you can use regular expressions to match just decimal characters.

select 'R011' ~ 'R\\d{3}'

http://www.postgresql.org/docs/9.2/static/functions-matching.html

Upvotes: 0

Related Questions