oracle sql query to get column values without space

Question

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.

You need to elaborate. Could you show some sample data and what the output would look like? — Adriano Carneiro
– Adriano Carneiro, Commented May 16, 2013 at 17:37

rs. · Accepted Answer · 2013-05-16 17:40:08Z

4

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

answered May 16, 2013 at 17:40

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

PM 77-1 · Accepted Answer · 2013-05-16 18:00:56Z

1

Oracle 10g and later:

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

Here's SQL Fiddle

edited May 16, 2013 at 18:00

answered May 16, 2013 at 17:48

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

3 Comments

Nomad Over a year ago

Thanks it gives error of invalid relational operator ora-00920

PM 77-1 Over a year ago

Sorry about that. Should've tested. Give me a few and I'll fix it.

Nomad Over a year ago

Thanks it works now. There are more than one correct answer. Since you provided sql fiddle. So i am accepting your answer. Thanks to every one for their help and time.

Sportball · Accepted Answer · 2013-05-16 17:39:19Z

1

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;

answered May 16, 2013 at 17:39

Sportball

9538 silver badges23 bronze badges

Comments

Ben · Accepted Answer · 2013-05-16 17:40:03Z

1

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

answered May 16, 2013 at 17:40

Ben

53.1k36 gold badges133 silver badges156 bronze badges

7 Comments

Nomad Over a year ago

I tried before with indices (1,2) it returns the data which has space in those two positions.

Ben Over a year ago

You're checking for one space though @Nomad, not two.

Nomad Over a year ago

you are right, thanks. But same result if i change indices to 1,3 instead of 1,2 etc.

Nomad Over a year ago

Thanks Ben for your answer and help.

Ben Over a year ago

Of course @Nomad, if you're testing for 3 spaces you have to check whether it's not equal to 3 spaces!

|

Collectives™ on Stack Overflow

oracle sql query to get column values without space

4 Answers 4

Comments

3 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related