How can I compare combination of 2 columns with out using Concatination operator || in oracle

sumanth_ora used Ask the Experts™
I have a need to compare combination of 2 columns as follows


Here Column_1 and Column_3 are data type number.
        Column_2 and Column_4 are data type Varchar.
Becuase of Concatenation, I am not be able to utilize Index on those columns. Could you please advise better way to write like (Column_1,Column_2) not in (Column_3,Column_4)

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

select * from yourTableName where column_1+'||'coumn_2<>column_3+'||'coumn_4


select * from YourTableName where column_1<>column_3 and column_2<>column_4

won't any of these work?

i would suggest  2nd point in ritesh statement..but i still have  modification on his point 1 and it's not recommended unless u r looking for sth different..

you can not concatenate 2 different data types( number and text ) unless you make both of them as one data type

select * from table1 where 
to_char(column_1)||coumn_2<> to_char(column_3)||coumn_4

Open in new window


you are right, I gave syntax for SQL Server forgot that Author wants it to check in Oracle. :)
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

I dont think indexes can be used while concat or type cast operation
Devinder Singh VirdiLead Oracle DBA Team

You can think of creating function based index on column1|| column2 and column3||column4.
How may table you are using? Are all columns belong to single table?
Without this infrmation only, we will not able to help you much.
Information Technology Specialist
Without using concatenation operator, see attached.


select * from table
where col1 != col3
   or col2 != col4;
The above query is not fultilling my requirement. since the data is data is laid down as follows
 col 1 ----is customer number who sold the product.
  col 2 ---is country code of the col1.
  col3--is customer number who Issued the gift card.
   col4 --is country code of the col3.
Here some times col1 and col 3 can be same but col1|| col2 <> col3 ||col4 will not be same. All columns are on the same table.  
SharathData Engineer

Can you provide some sample data and the way you want to compare?
awking00Information Technology Specialist

>>Here some times col1 and col 3 can be same but col1|| col2 <> col3 ||col4 will not be same. All columns are on the same table.<<
Which implies that col2 <> col4, so I'm not sure why my query wouldn't work. Please provide some sample data, as Sharath_123 suggested, and your expected results so we can get a better idea of your problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial