CODEWITHSUNDEEP
selectplsqlwhitespace
I Z
I Z

Reputation: 5927

Oracle PL/SQL: How do I filter out whitespaces in SELECT?

I have a table mytable that has a column ngram which is a VARCHAR2. I want to SELECT only those rows where ngram does not contain any whitespaces (tabs, spaces, EOLs etc). What should I replace <COND> below with?

SELECT ngram FROM mytable WHERE <COND>;

Thanks!

Upvotes: 0

Views: 2539

Answers (3)

Gary_W
Gary_W

Reputation: 10360

This should match ngram where it contains no whitespace characters by using the \s shorthand for all whitespace characters. I only tested by inserting a TAB into a string in a VARCHAR2 column and it was then excluded:

where regexp_instr(ngram, '\s') = 0;

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

You could use regexp_instr (or regexp_like, or other regexp functions), see here for example

  where  regexp_instr(ngram, '[ '|| CHR(10) || CHR(13) || CHR(9) ||']') = 0

the white space is managed here '[ '

  • chr(10) = line feed
  • chr(13) = carriage return
  • chr(9) = tab

Upvotes: 1

void
void

Reputation: 7890

you can use CHR and INSTR function ASCII code of the characters you want to filter for example your where clause can be like this for an special character:

 INSTR(ngram,CHR(the ASCI CODE of special  char))=0

or the condition can be like this:

where 
and ngram not like '%'||CHR(0)||'%' -- for null
.
.
.
and ngram not like '%'||CHR(31)||'%' -- for unit separator
and ngram not like '%'||CHR(127)||'%'-- for delete

here you can get all codes http://www.theasciicode.com.ar/extended-ascii-code/non-breaking-space-no-break-space-ascii-code-255.html

Upvotes: 1

Related Questions