CODEWITHSUNDEEP
sqloracle-databaseplsql
Nomad
Nomad

Reputation: 1102

oracle sql query to get column values without space

I am trying to run a query to select values from a column in a table, which doesn't have space at specific position in the data in the column. i.e. first two positions.

Select test_col from A where SUBSTR(test_col, 0 ,2) <> ' '

So far, it's returning columns with have space in the beginning two position.

Any Suggestion.

Example:

test_col
  Quick Brown Fox
Black Sheep
  This a test
Mary had a little lamb

So the query should return Black Sheep and Mary had a little lamb.

Upvotes: 1

Views: 4371

Answers (4)

PM 77-1
PM 77-1

Reputation: 13334

Oracle 10g and later:

SELECT test_col FROM a WHERE REGEXP_LIKE(test_col, '^[^ ]{2}.*$');

Here's SQL Fiddle

Upvotes: 1

rs.
rs.

Reputation: 27427

Your index should start with 1 and not 0 and I prefer Trim and check for null over checking for space in data. Try this

Select test_col from A where Trim(SUBSTR(test_col, 1 ,2)) IS NOT NULL

Upvotes: 4

Ben
Ben

Reputation: 52863

The first positional index of a string is 1, not 0 (though SUBSTR() treats it as 1) and you're only testing for one space:

select test_col from A where SUBSTR(test_col, 1, 2) <> '  '

Upvotes: 1

Michael O&#39;Neill
Michael O&#39;Neill

Reputation: 954

select test_col 
  from A 
 where SUBSTR(test_col, 1, 1) <> ' '
   and SUBSTR(test_col, 2, 1) <> ' ';

OR (even better)

select test_col 
  from A 
 where INSTR(SUBSTR(test_col, 1, 2), ' ') = 0;

Upvotes: 1

Related Questions