Question 186282 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to check if a value is made of spaces in SQL?

I’m trying to make a query in my PostgreSQL database that checks whether an optional string value exists or not. How do I do that, taking into account my database always fill out the full size of the column with spaces? Can I use RegEx?

I’ve started trying using the following:

SELECT count(token) > 0
FROM table
WHERE user = 'user';

However, it always returns true, even when there’s no token there.

Tags:

2

Answers


  1. 0

    If your PostgreSQL database always fills out the full size of the column with spaces, you can modify your query to handle this scenario. Instead of checking if the string value is greater than zero, you can trim the spaces and check if the resulting string is not empty. Here’s an example of how you can do that:

    SELECT trim(token) <> '' 
FROM table
WHERE user = 'user';

    In this modified query, the trim() function removes leading and trailing spaces from the token column. Then, it checks if the resulting string is not empty (<> ''). This will return true if there is a non-empty value in the token column for the specified user, and false otherwise.

    Using regular expressions (RegEx) is another option. You can leverage the ~ operator in PostgreSQL to match the presence of any non-space character in the token column. Here’s an example:

    SELECT token ~ 'S'
FROM table
WHERE user = 'user';

    In this case, the regular expression S matches any non-space character. If the token column contains at least one non-space character, the result will be true; otherwise, it will be false.

    Login or Signup to reply.
  2. 0

    To check if a value consists only of spaces in SQL, you can use the trim() function along with a comparison.

    SELECT count(token) > 0
FROM table
WHERE user = 'user' AND trim(token) <> '';

    The trim() function removes leading and trailing spaces from the token value. Then, the comparison <> ” checks if the trimmed value is not an empty string. If the value consists only of spaces, the trimmed value will be an empty string, and the condition will evaluate to false.

    By adding this condition, the query will return true only if the token value contains non-space characters. If the token value is either an empty string or consists solely of spaces, the query will return false.

    Note that the trim() function may vary depending on the specific SQL database you are using. The syntax provided above applies to PostgreSQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search