Postgresql : Search field contain tab character. (With LIKE)

Question

I'm searching how to check if any field contains the TAB character.

after read this post, I tried to use this command :

SELECT id FROM account WHERE description LIKE '%\t%';

But it returns me all fields that contain the 't' character.

Do you have any solution to represent the TAB character ?

Answer 1

you have to use a literal tab chacater,

 SELECT id FROM account WHERE description LIKE '%   %';

In psql type ctrl-V and then TAB to enter a tab. in other environments there are other ways to enter literal tabs.

Alternatively you can use escape string syntax: e'%\t%', or octal escape e'%\011%'.

Answer 2

In SQL there is no "escaping" of characters like \t. You can use the chr() function for this:

select id
from account
where description LIKE '%'||chr(9)||'%'

I prefer the strpos function in this case, because I think it makes the intention clearer

select id
from account
where strpos(description, chr(9)) > 0

Answer 3

I'm very late to this party, but having looked into this today, I've found you can also use regular expression.

SELECT id FROM account WHERE description ~ '\t';