Comparing a column with itself

Database
Enthusiast

Comparing a column with itself

Hi!

I have a reuqirement where I have to compare a column to itsellf. Basically I have to find out all the TXT fields where the first n characters match.

eg. of TXT- suppose we need to match the first 4 chars .
ABCD123
ABCDTYU
DEFG123
DEFH123
ABCD234

The out put should be
ABCD123 , ABCDTYU,
ABCD123 ,ABCD234

Thanks!

3 REPLIES
Enthusiast

Re: Comparing a column with itself

Hi Tnewbee,
Try this:

Select T1.ColumnName, T2.ColumnName
From TablaName T1
Cross join TablaName T2
Where substring( T1.ColumnName,1,4) = substring( T2.ColumnName,1,4) and
T1.ColumnName <> T2.ColumnName

Regards.
Enthusiast

Re: Comparing a column with itself

This is exactly what I tried but the query spools out because its a large table. any way to get rid of the spool error?
Junior Contributor

Re: Comparing a column with itself

Your example result set shows
ABCD123 , ABCDTYU
ABCD123 , ABCD234

but why is this not also a valid row?
ABCD234 , ABCDTYU

How many rows might exist with the same n characters?

Would this be helpfull?
select * from tab
qualify min(col) over (partition by substring(col from 1 for 4)
<> max(col) over (partition by substring(col from 1 for 4)

If you still need your expected result set with columns instead of rows, you could do some fancy self join based on this plus a row_number.

Dieter