Get all Unique charactres from a column in table - SQL

Question

i have a reqiurement to find out all unique characters avialable in a given column from table

following is the content in a coulmn

SNO  NOTE_TEXT (column Name)
------------------------
1    Recovery for 1125.00 voided due to incorrect re-insurance allocation. 
     Now booked to 2 FR3270.
2    salvage cheque awaited
3    you. cdm_char_13  cdm_char_10  cdm_char_13  cdm_char_10 It is at:       
     cdm_char_13cdm_char_10 Walmgate Road Police Car Pound cdm_char_13  cdm_char_10
     Walmgate Road cdm_char_13  cdm_char_10 Perivale cdm_char_13  cdm_char_10 London 
     cdm_char_13  cdm_char_10 UB6 7LR

Requirement:-

Need to identify all unique characters avilable in NOTE_TEXT column output shoud look like

A
B
C
.
.
Z
a
.
.
z
0
1
2
.
9
~
!
@
#
$
.
.
.
.

like this all avilabe unique characters from the table column

Answer 1

Try this code -

CREATE TABLE [dbo].[table1](
    [column1] [varchar](50) NULL
)
GO
INSERT INTO [dbo].[table1] VALUES ('abc')
INSERT INTO [dbo].[table1] VALUES ('acbd')
INSERT INTO [dbo].[table1] VALUES ('cbde')
GO

DECLARE @column1 varchar(50);
DECLARE @text varchar(max);
DECLARE @i int;
DECLARE cur CURSOR FOR SELECT column1 FROM dbo.table1;
SET @text = '';
OPEN cur;
FETCH NEXT FROM cur INTO @column1;
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @i = 1;
    WHILE @i <= LEN(@column1) BEGIN
        IF CHARINDEX(SUBSTRING(@column1, @i, 1), @text) = 0
            SET @text = @text + SUBSTRING(@column1, @i, 1);
        SET @i = @i + 1;
    END;
    FETCH NEXT FROM cur INTO @column1;
END;
CLOSE cur;
DEALLOCATE cur;
SELECT @text;
GO
------------------------------------------------------
abcde
(1 row(s) affected)

Answer 2

General approach: create a table of characters, 'CROSS JOINto yourNotestable, replace the character from the characters table with the empty string and use the respective lengths to calculate the number of occurrences for that row, thenSUM...GROUP BY` each character.

I don't know Sybase DB so here's some Standard SQL (well, except for the REPLACE() function, which most SQLs have an equivalent of):

WITH Notes (SNO, NOTE_TEXT)
     AS
     (
      SELECT SNO, CAST(NOTE_TEXT AS VARCHAR(200))
        FROM (
              VALUES (1, 'Recovery for 1125.00 voided due to incorrect re-insurance allocation. Now booked to 2 FR3270.'), 
                     (2, 'salvage cheque awaited'), 
                     (3, 'you. cdm_char_13  cdm_char_10  cdm_char_13  cdm_char_10 It is at: cdm_char_13 cdm_char_10 Walmgate Road Police Car Pound cdm_char_13  cdm_char_10 Walmgate Road cdm_char_13  cdm_char_10 Perivale cdm_char_13  cdm_char_10 London cdm_char_13  cdm_char_10 UB6 7LR')
             ) AS Notes (SNO, NOTE_TEXT)
     ), 
     Characters (chars)
     AS
     (
      SELECT CAST(chars AS CHAR(1))
        FROM (
              VALUES ('A'), 
                     ('B'), 
                  --  ... 
                     ('Q'), 
                  --  ... 
                     ('Y'), 
                     ('Z')
             ) AS Characters (chars)
     ), 
     CharacterNoteTallies (SNO, NOTE_TEXT, chars, chars_tally)
     AS
     (
      SELECT N1.SNO, N1.NOTE_TEXT, C1.chars, 
             DATALENGTH(N1.NOTE_TEXT) 
                - DATALENGTH(REPLACE(N1.NOTE_TEXT, C1.chars, '')) 
                AS chars_tally
        FROM Notes AS N1
             CROSS JOIN Characters AS C1
     )
SELECT T1.chars, SUM(T1.chars_tally) AS chars_tally
  FROM CharacterNoteTallies AS T1
 GROUP
    BY T1.chars;