Multi tool use

Search first character in a column PostgreSQL

I want to search the first character from a column by charlist (bracket expression) but it brings all the column characters although there are customers their names starting with non-letters.

I use PostgreSQL.

SELECT name
FROM customs
WHERE name ~* '[a-z]'


2 Answers
2

You could also dispense with regular expression matching and simply do:

where name < 'a' or name >= '{'


{ is the mysterious character that follows z in the ASCII chart. Note: For this or any solution, you may need to check whether or not the collation is case-sensitive.

{

z

https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP:

Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.

LIKE

Some examples:

'abc' ~ 'abc' true
'abc' ~ '^a' true
'abc' ~ '(b|d)' true
'abc' ~ '^(b|c)' false

So your condition should be

WHERE name ~* '^[a-z]'


if you want to match only at the beginning of name.

name

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.