Find value having leading and trailing space in DB table column - Oracle

Question

I have a record which has value with leading and space in a column.

eg: column value is ' D0019 '

I want to pass this particular column in where clause.

select * from my_table where my_column='D0019';

Since the value has space, it doesn't detect from the where clause.

How can I select the record even it has leading and trailing spaces in the value?

My DB is ORACLE

========================================

UPDATE :

I get value only when I try

select * from my_table where my_column like '%D0019%'

not even with ' %D0019% '

=============================================

UPDATE 2 :

SELECT my_column  ,DUMP(my_column) FROM my_table  WHERE my_column like '%D0019';

output is

"   D0019"  Typ=1 Len=6: 9,68,48,48,49,57

Use trim(my_column) in your query.. This function removes leading and trailing spaces. — San
– San, Commented Nov 24, 2014 at 11:11
@San trim did not work for me. This is why I raised this question here. also I don't want to check the values with space or like condition. I want to find the exact value just removing spaces. — user2771655
– user2771655, Commented Nov 24, 2014 at 11:24
@user2771655 can you try this and paste the output in your question ? SELECT my_column ,DUMP(my_column) FROM my_table WHERE my_column like '%D0019%'; I guess, it is tabspace or something else. Let's check the ascii value stored. — Maheswaran Ravisankar
– Maheswaran Ravisankar, Commented Nov 24, 2014 at 11:27
@MaheswaranRavisankar Here is the output " D0019" Typ=1 Len=6: 9,68,48,48,49,57 — user2771655
– user2771655, Commented Nov 24, 2014 at 11:32

Maheswaran Ravisankar · Accepted Answer · 2014-11-24 12:38:21Z

1

it's not the normal space you have to remove. It's Horizontal Tab character . (Ascii 9).

The below regexp would strip all the charcters from ASCII range 0-32 , which are associated with the white space symbols.

select * from my_table
 WHERE
     REGEXP_REPLACE(my_column,'['||chr(1)||'-'||chr(32)||']' ) = 'D0019';

1 Comment

user2771655 Over a year ago

it says *Cause: The regular expression did not have balanced brackets.

score 1 · Accepted Answer · 2014-11-24 13:26:10Z

1

select *
from my_table
where trim(my_column) = 'D0019';

Edit:

Based on the output of the dump() function your values not just contain (leading) spaces but also a tab character. the trim() function will only remove space characters, but not other whitespace.

In order to get rid of any whitespace at the beginning you will need to use regexp_replace()

select *
from my_table
where regexp_replace(my_column,'^(\s)+','') = 'D0019'

If you need to get rid of leading and trailing whitespace, the regex needs to be expanded:

select *
from my_table
where regexp_replace(my_column,'^(\s)+|(\s)+$','') = 'D0019'

SQLFiddle example: http://sqlfiddle.com/#!4/3326a/1

edited Nov 24, 2014 at 13:26

answered Nov 24, 2014 at 11:12

user330315

1 Comment

user2771655 Over a year ago

I'm not sure what else to provide you. I Don't know how this value is stored in the DB. If so I have to find it through the project which is not in my scope. Pls help to find the issue here.

Cyryl1972 · Accepted Answer · 2014-11-24 14:06:07Z

1

Seems as simple as (if I get it):

select * from TAB where REGEXP_LIKE (COL,'\s*D0019')

returns values such ' D0019', 'D0019', ' D0019', ' D0019' ...etc.

answered Nov 24, 2014 at 14:06

Cyryl1972

6244 silver badges8 bronze badges

Comments

Semih Eker · Accepted Answer · 2014-11-24 11:11:38Z

0

Try like this;

    select * from my_table where my_column like '%D0019';

answered Nov 24, 2014 at 11:11

Semih Eker

2,4071 gold badge21 silver badges29 bronze badges

1 Comment

user2771655 Over a year ago

I want to find exact value D0019

RudiDudi · Accepted Answer · 2014-11-24 11:14:28Z

0

you can also use this if you want only spaces at start for the select:

select * from my_table where my_column like '% %D0019';

or

select * from my_table where my_column like ' %D0019';

answered Nov 24, 2014 at 11:14

RudiDudi

4551 gold badge8 silver badges18 bronze badges

1 Comment

user2771655 Over a year ago

First query doesn't work but second one. Yet I don't want to use the '%'

Collectives™ on Stack Overflow

Find value having leading and trailing space in DB table column - Oracle

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related