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 ?

or you can use hex value: 0x09 :D... – HellBaby Dec 23 '14 at 08:57 — HellBaby, Dec 23 '14 at 08:57

score 23 · Accepted Answer · answered Dec 23 '14 at 08:52

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

Jasen · Answer 2 · 2014-12-23T08:59:26.427

5

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%'.

edited Dec 23 '14 at 08:59

answered Dec 23 '14 at 08:54

Jasen

score 3 · Answer 3 · answered Dec 17 '19 at 22:21

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';

answered Dec 17 '19 at 22:21

Andy Norris

Indeed you can, postgresql internally converts the LIKE expression to regex. the question asked for a solution using LIKE but the accepted answer uses a different method. – Jasen Aug 03 '20 at 19:15
1

Having a literal tab in the query is dangerous if you have other coders who may open the file and convert tabs to spaces. – Andy Norris Aug 18 '20 at 06:17

3 Answers3