Check if variable contains x in PostgreSQL function

Question

In a PostgreSQL function, what is the syntax to check if a variable passed in contains a specific letter?

The variable being passed to the function is comma separated list of letters and I want to check if this list contains the letter a or x.

I imagine the code would look something like this:

-- var typically looks like 'a,b,c,x'
if (some way of stripping anything other than a and x from var) ~* [ax]
  -- Do something else
end if;

I assume regex is the answer, I'm just not 100% sure about the syntax.

Thanks!

Ihor Romanchenko · Accepted Answer · 2013-10-02 14:58:44Z

5

The easiest way to check if a string contains a substring or a letter is

if ( var like '%x%')
  -- Do something else
end if;

Or you can use regexp as you mentioned:

if ( var ~* 'x')

answered Oct 2, 2013 at 14:58

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Oops - sorry, I had a typo. I need to check for a OR x. Thanks for your reply!

I ended up just using if (var ~* 'a' OR var ~* 'x') using what you confirmed in your second example. Thanks again for your help!

@Matt You can use var ~* 'x|a'

The like comparison is about 9 times slower than the regular expression.