Here's a straightforward approach to extract the required patterns from your Column A and count their occurrences:

First, let's create a sample table to test with your data (skip this if you're working with your existing table):

CREATE TABLE SampleData (ColumnA VARCHAR(100));
INSERT INTO SampleData VALUES
('abc.(Backbridge)'),
('bac.(Not Required)'),
('abxsa.<sb not available in ont>.sbb'),
('kcr.<sb not available in ont>.tdp'),
('hikh.<sb not available in ont>.nji'),
('sac.(Not Required)');

Then use this query to get your desired output:

SELECT 
    LTRIM(RTRIM(CASE
        WHEN CHARINDEX('(', ColumnA) > 0 THEN SUBSTRING(ColumnA, CHARINDEX('(', ColumnA) + 1, CHARINDEX(')', ColumnA) - CHARINDEX('(', ColumnA) - 1)
        WHEN CHARINDEX('<', ColumnA) > 0 THEN SUBSTRING(ColumnA, CHARINDEX('<', ColumnA) + 1, CHARINDEX('>', ColumnA) - CHARINDEX('<', ColumnA) - 1)
        ELSE ColumnA -- Fallback for rows without either pattern
    END)) AS [Column B],
    COUNT(*) AS Counts
FROM SampleData -- Replace with your actual table name
GROUP BY 
    LTRIM(RTRIM(CASE
        WHEN CHARINDEX('(', ColumnA) > 0 THEN SUBSTRING(ColumnA, CHARINDEX('(', ColumnA) + 1, CHARINDEX(')', ColumnA) - CHARINDEX('(', ColumnA) - 1)
        WHEN CHARINDEX('<', ColumnA) > 0 THEN SUBSTRING(ColumnA, CHARINDEX('<', ColumnA) + 1, CHARINDEX('>', ColumnA) - CHARINDEX('<', ColumnA) - 1)
        ELSE ColumnA
    END))
ORDER BY Counts DESC;

How This Works: #

1. CASE Statement: Checks which delimiter exists in each row (( or <):
   • For strings with parentheses: Extracts the text between ( and ) using SUBSTRING, adjusting positions to exclude the delimiters themselves.
   • For strings with angle brackets: Extracts the text between < and > using the same logic.
2. LTRIM/RTRIM: Cleans up any accidental leading/trailing whitespace from the extracted text.
3. GROUP BY & COUNT: Groups the extracted values and counts how many times each appears.

Notes: #

• Case Sensitivity: The query preserves the case of the original text (e.g., Not Required instead of Not required). If you need case-insensitive counting or output, wrap the extracted value in LOWER() or UPPER() in both the SELECT and GROUP BY clauses.
• Edge Cases: This assumes each row has either parentheses or angle brackets (not both, and no nested delimiters). If your data has more complex patterns, you might need to adjust the logic (e.g., using PATINDEX for regex-like matching).

内容的提问来源于stack exchange，提问作者beckham