Same record with Space and Without Space [message #654291] |
Fri, 29 July 2016 07:47 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
In my table I have record with trailing space and without space.
Ex.'G1979-64000'
Material_Number
'G1979-64000'
'G1979-64000 '
I want to find out the records with same material_number but without spaces.
Request your help to find out these records.
Thanks.
Regards,
pstanand.
|
|
|
|
Re: Same record with Space and Without Space [message #654295 is a reply to message #654292] |
Fri, 29 July 2016 08:03 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
Below is my table structure and the data.
If the data is like with and without spaces how can I select those records without spaces.
Please suggest me. Below is the sample data I gave, but it contains 5 lacs records.
create table desc_table (
material_number varchar2(18) not null,
description varchar2(50),
language_code varchar2(2) not null,
constraint pk_mat_lang_cd primary key (material_number,language_code))
/
insert into desc_table values('ABC', null,'EN')
/
insert into desc_table values('ABC ',null,'EN')
/
commit
/
select * from desc_table where material_number like 'ABC%'
/
appreciate your help.
|
|
|
|
Re: Same record with Space and Without Space [message #654297 is a reply to message #654296] |
Fri, 29 July 2016 08:45 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi BlackSwan,
Please see the below code. I have used regexp_replace function and able to get the records with space and without space.
Is the approach is correct? Can you suggest?
insert into desc_table values('ABC',null,'EN')
/
insert into desc_table values('ABC ','with space','EN')
/
insert into desc_table values('123',null,'IT')
/
insert into desc_table values('123 ','with space','IT')
/
commit
/
select regexp_replace(material_number||chr(9)||'foo', '[[:space;]]',' ')material_number,
description,language_code
from desc_table
/
This is the result I have got.
MATERIAL_NUMBER DESCRIPTION LANGUAGE_CODE
ABCfoo EN
ABC foo with space EN
123foo IT
123 foo with space IT
|
|
|
|
|
|
|
|
|
|
|
Re: Same record with Space and Without Space [message #654337 is a reply to message #654334] |
Sun, 31 July 2016 03:33 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> select rowid, material_number,
2 case when material_number = trim(material_number)
3 then 'without space'
4 else 'with space'
5 end as space
6 from desc_table
7 order by material_number
8 /
ROWID MATERIAL_NUMBER SPACE
------------------ --------------- -------------
AAAgZSAAGAAArJGAAC 123 without space
AAAgZSAAGAAArJGAAD 123 with space
AAAgZSAAGAAArJGAAA ABC without space
AAAgZSAAGAAArJGAAB ABC with space
4 rows selected.
|
|
|