How to split and display distinct letters from a word in SQL?

sql

Solution

Well, here is my solution (sqlfiddle) - it aims to use a "Relational SQL" operations, which may have been what the interviewer was going for conceptually.

Most of the work done is simply to turn the string into a set of (pos, letter) records as the relevant final applied DQL is a mere SELECT with a grouping and ordering applied.

select letter
from (
  -- All of this just to get a set of (pos, letter)
  select ns.n as pos, substring(ss.s, ns.n, 1) as letter
  from (select 'MANHATTAN' as s) as ss
  cross join (
    -- Or use another form to create a "numbers table"
    select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9)) as X(n)
    ) as ns
  ) as pairs
group by letter    -- guarantees distinctness
order by min(pos)  -- ensure output is ordered MANHT

The above query works in SQL Server 2008, but the "Numbers Table" may have to be altered for other vendors. Otherwise, there is nothing used that is vendor specific - no CTE, or cross application of a function, or procedural language code ..

That being said, the above is to show a conceptual approach - SQL is designed for use with sets and relations and multiplicity across records; the above example is, in some sense, merely a perversion of such.

Examining the intermediate relation,

  select ns.n as pos, substring(ss.s, ns.n, 1) as letter
  from (select 'MANHATTAN' as s) as ss
  cross join (
    select n from (values (1),(2),(3),(4),(5),(6),(7),(8),(9)) as X(n)
    ) as ns

uses a cross join to generate the Cartesian product of the string (1 row) with the numbers (9 rows); the `substring` function is then applied with the string and each number to obtain each character in accordance with its position. The resulting set contains the records-

POS LETTER
1   M
2   A
3   N
..
9   N

Then the outer select groups each record according to the letter and the resulting records are ordered by the minimum (first) occurrence position of the letter that establishing the grouping. (Without the order by the letters would have been distinct but the final order would not be guaranteed.)

Problem

Original source