Home » SQL & PL/SQL » SQL & PL/SQL » Same record with Space and Without Space (Oracle 12c, Windows NT)
Same record with Space and Without Space [message #654291] Fri, 29 July 2016 07:47 Go to next message
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 #654292 is a reply to message #654291] Fri, 29 July 2016 07:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Same record with Space and Without Space [message #654295 is a reply to message #654292] Fri, 29 July 2016 08:03 Go to previous messageGo to next message
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 #654296 is a reply to message #654295] Fri, 29 July 2016 08:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
you can use TRANSLATE() function to remove space characters & then SELECT rows where length of column equals length of column with the spaces characters removed.
Re: Same record with Space and Without Space [message #654297 is a reply to message #654296] Fri, 29 July 2016 08:45 Go to previous messageGo to next message
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 #654298 is a reply to message #654297] Fri, 29 July 2016 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is the approach is correct?
It is you table, your data & your requirements.
You decide if does what you want it to do.
It is not my call to make.
Re: Same record with Space and Without Space [message #654300 is a reply to message #654298] Fri, 29 July 2016 09:16 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd use trim rather than regexp_replace
Re: Same record with Space and Without Space [message #654301 is a reply to message #654300] Fri, 29 July 2016 09:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what should be done with below?

insert into desc_table values('Jones & Sons',null,'EN')
Re: Same record with Space and Without Space [message #654302 is a reply to message #654301] Fri, 29 July 2016 09:28 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
I read the OPs issue as being with trailing spaces, not spaces within the string (I'm guessing similar to how cm reads the issue).
Re: Same record with Space and Without Space [message #654304 is a reply to message #654302] Fri, 29 July 2016 10:59 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I agree about the TRIM. Regular expressions take more computing power and time then a simple trim. For example

select * from desc_table where trim(material_number) = 'G1979-64000';

[Updated on: Fri, 29 July 2016 10:59]

Report message to a moderator

Re: Same record with Space and Without Space [message #654307 is a reply to message #654291] Fri, 29 July 2016 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

@pstanand,

Don't forget to feedback, here and in your previous topics.

Re: Same record with Space and Without Space [message #654334 is a reply to message #654307] Sun, 31 July 2016 00:13 Go to previous messageGo to next message
pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India
Senior Member
Hi,
Since I know the material_number I can use the TRIM, but for the unknown numbers how can I identify?
Could you please show some light on this?

Thanks.
Re: Same record with Space and Without Space [message #654336 is a reply to message #654334] Sun, 31 July 2016 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What does this mean?

Re: Same record with Space and Without Space [message #654337 is a reply to message #654334] Sun, 31 July 2016 03:33 Go to previous message
Barbara Boehmer
Messages: 8922
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.
Previous Topic: comparison ALL with subquery returns no rows - TRUE
Next Topic: Error An INTO Clause
Goto Forum:
  


Current Time: Sun Jun 13 14:52:22 CDT 2021