CODEWITHSUNDEEP
stringpostgresqlspace
John Macl
John Macl

Reputation: 331

find space in string postgresql

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.

Upvotes: 1

Views: 23357

Answers (2)

João Víctor Melo
João Víctor Melo

Reputation: 190

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

Upvotes: 3

Robert H
Robert H

Reputation: 11730

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.

Upvotes: 2

Related Questions