获取从列的所有独特charactres表 - SQL

问题描述：

我有一个reqiurement找出从表获取从列的所有独特charactres表 - SQL

下一个给定列可用的编缉所有独特的字符是一个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

要求内容： -

需要确定NOTE_TEXT列即时拍摄的所有独特字符输出768,16样子

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

个

这样所有从表列

RDBMS你使用的是哪个，什么是数据类型的NOTE_TEXT？ – 2010-09-23 05:57:59

我们正在使用Sybase DB – user455818 2010-09-23 06:06:03

答

试试这个代码avilabe独特的字符 -

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)

答

一般方法：创建人物的表，“CROSS JOIN to your注table, 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, then SUM ... GROUP BY`每个角色。

我不知道的Sybase数据库所以这里的一些标准SQL（当然，除了REPLACE()功能，其中大部分SQLS具有的等效）：

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;

获取从列的所有独特charactres表 - SQL

相关推荐