How to find space after words in postgresql:
I have two same strings in my database :
string1
string1
I am trying to find the one with 2 spaces before and one space after.
here are some of the queries I used with their results:
SELECT * FROM table WHERE "column" LIKE '__string1_'; --> *no result*
SELECT * FROM part1 WHERE "column" LIKE '__string1%';
Results:
1) string1 and xyx
2) string1 and string2
3) string1
but I only need string1 with no strings after or before.
I will just complement the above answer:
Suppose you want to find any space in the column Name in the demo table, then the code would be like:
SELECT * FROM demo
WHERE Name LIKE '% %'
ORDER BY Name
Now, if you want any string 'a' for example inside the column, you would just have the following:
SELECT * FROM demo
WHERE Name LIKE '%a%'
ORDER BY Name
and also, for words that begin with a space, you would just use '_' (in a certain position):
SELECT * FROM demo
WHERE Name LIKE '_a%'
ORDER BY Name
There are likely several ways to accomplish this. See PostgreSQL's pattern matching documentation for some examples.
However, I use % to find patterns: select * from table where column ILIKE '%string1%'; would return anything with string1 in it, including the cols with spaces.
You can also try escaping the spaces: select * from table where column ILIKE '\ \ string1\ ';
or, even simpler select * from table where column ILIKE ' string1';
I also use the case insensitive ILIKE as an alternative for a case sensitive LIKE, so case will not matter in your query.
\ is an escape character, so its escaping the space and ensures it gets processed as a character and not whitespace. Ensure its \ \ (there is a space between the slashes and one after).
' string1'?