New to SQL, and having problems coming up with Query that will show an acceptable result.

Say I have a table that has 3 strings:

• 11111A111B1
• 22222A222B2
• 33333A333B3

All strings have the same number of characters.

I want a result that shows which characters are the same and at what position. So using the example above, the result should show something like the 6th character A and 10th character B are the same in the table.

CodePudding user response：

You said your string lengths are all the same. And you also don't need to return the actual string, but rather, which character is found in the same position of more than one string. You could UNION a bunch of queries and then select from it.

create table my_data (
  id integer, 
  string_column varchar(20)
  );
  
insert into my_data values 
(1, '11111A111B1'), 
(2, '22222A222B2'), 
(3, '33333A333B3');
 

select position, string_char, t_count
from (
  select 'pos1' as position, substring(string_column, 1, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos2' as position, substring(string_column, 2, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos3' as position, substring(string_column, 3, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos4' as position, substring(string_column, 4, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos5' as position, substring(string_column, 5, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos6' as position, substring(string_column, 6, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos7' as position, substring(string_column, 7, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos8' as position, substring(string_column, 8, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos9' as position, substring(string_column, 9, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos10' as position, substring(string_column, 10, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  union
  select 'pos11' as position, substring(string_column, 11, 1) as string_char, count(id) as t_count
  from my_data
  group by 1,2
  )z
where t_count > 1;

UPDATE

And if you want to require that character and position to be the same for ALL rows, then change >1 to =nbr of rows you have.

View on DB Fiddle