Skip to Main Content
  • Questions

  • Use select query to concatinate, and remove spaces from two columns

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, prashant.

Asked: August 04, 2016 - 11:30 pm UTC

Last updated: August 08, 2016 - 4:48 pm UTC

Version: 10.2.0.1.0

Viewed 1000+ times

You Asked

Compare table with spaces in between data, with table without spaces

Example:-


Table A has three fields: ID, Cd_1, and Cd_2

Fields: Cd_1 or Cd_2 can have spaces between the codes. Below example has space between X and Y in Cd_1


ID Cd_1 Cd_2

1---X Y---Z



Table B has two fields: ID, Cd

Table B is supposed to populate codes (non-spaces) from Cd_1, and Cd_2 from Table A

Example:

ID Cd
1 X
1 Y
1 Z


I need to Select write a query in table A, so it will match output in table B

and Chris said...

So you need to convert a list of characters into rows?

Here's one way to do this using Stew Ashton's XML splitting technique. 

create table t1 (
  x int,
  y varchar2(10),
  z varchar2(10)
);
insert into t1 values (1, 'X  Y', 'Z');
insert into t1 values (2, 'A B    C', 'D   E F');

select x, subs from t1, xmltable(
  '/r/c/text()'
  passing xmltype('<r><c>'||regexp_replace(y, '\s+', '</c><c>')||'</c>
 <c>'||regexp_replace(z, '\s+', '</c><c>')||'</c></r>')
  columns subs varchar2(4000) path '.'
);

X  SUBS  
1  X     
1  Y     
1  Z     
2  A     
2  B     
2  C     
2  D     
2  E     
2  F

The key is wrap all the letters in XML tags such as <c> </c>. The regexp_replace looks for all the spaces and does just this.

For more details on how this works, read:

https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Needed something else (I should have been more clear on question)

prashant dhital, August 05, 2016 - 2:40 pm UTC

Hi Tom,

I should have been more clear on my question.

I do not have DDL access, and I am supposed to ONLY use SQL script on table A to compare with table B.

Thanks
Prashant
Chris Saxon
August 08, 2016 - 4:48 pm UTC

You don't need DDL access? Replace T1 in the SQL above with your table name.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.