CODEWITHSUNDEEP
whitespacespacepsql
user2301195
user2301195

Reputation: 35

psql query to return values that contain space at the start/end of the value specified

I have a DB with more than 1,000,000 entries in it, some of them contain space char at the start/end of the value.

I have tried the following queries and it works but i would have to go through 1,000,000 records because all id's are unique 

select * from tablename where id like '%1234%';
select * from tablename where id='1234 ';
select * from tablename where id=' 1234';
select * from tablename where id=' 1234 ';

is there a query that can be run that returns all values with space/empty char at the start/end of the value?

Appreciate your help B

Upvotes: 3

Views: 9301

Answers (2)

Herman J. Radtke III
Herman J. Radtke III

Reputation: 1812

You can use a regular expression that says "one or more spaces at the start of the string OR one of more spaces at the end of the string".

select * from tablename where id ~ '^\s+|\s+$';

Each special character:

  • ^ match start of the string
  • \s match a white space character
  • | the or operator
  • + one or more times
  • $ match the end of the string.

Upvotes: 8

user2301195
user2301195

Reputation: 35

I found the way to do this

select * from tablename where id like '% %';

Upvotes: -3

Related Questions