CODEWITHSUNDEEP
sqlsql-serverdistinct
B.Balamanigandan
B.Balamanigandan

Reputation: 4875

Extract Distinct Special Characters in a Column using SQL Select

I'm having a Table Company. I need to know what are all the Special Characters are in the Company Names.

Consider the Sample Table

S.No.     CompanyName
__________________________________
1.        24/7 Customers
2.        Rose & Co.
3.        Rose Inc. Corp.
4.        Rose Pvt. Ltd.,

From the above table I need the Select only the Distinct Special Characters to know what are all the special characters are involved in the CompanyName

The Output of the above table should be /&.,

Upvotes: 0

Views: 2440

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this,

declare @t table(SNo int,CompanyName varchar(30))
insert into @t VALUES
(1,'24/7 Customers')
,(2,'Rose & Co.')
,(3,'Rose Inc. Corp.')
,(4,'Rose Pvt. Ltd.,')

;With CTE as
(
select sno
,stuff(CompanyName,PATINDEX('%[,-@\.&/]%',CompanyName),1,'') CompanyName
,SUBSTRING(CompanyName,PATINDEX('%[,-@\.&/]%',CompanyName),1) spcol from @t 
union ALL

select sno,
replace(CompanyName,SUBSTRING(CompanyName,PATINDEX('%[,-@\.&/]%',CompanyName),1),'') 
,SUBSTRING(CompanyName,PATINDEX('%[,-@\.&/]%',CompanyName),1)
from cte
where PATINDEX('%[,-@\.&/]%',CompanyName)>0
)

select distinct spcol

from cte

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

There is one place where you need to define your "regular" characters

select  '%[^a-zA-Z0-9 ]%'

with    prm (regular_char)
        as
        (
            select  '%[^a-zA-Z0-9 ]%'
        )

       ,cte (special_char,string_suffix)
        as
        (
            select  ''              as special_char
                   ,CompanyName     as string_suffix

            from    t

            union all

            select  substring (string_suffix,special_char_ind,1)                    as special_char
                   ,substring (string_suffix,special_char_ind+1,len(string_suffix)) as string_suffix

            from   (select  string_suffix
                           ,nullif(patindex(prm.regular_char,string_suffix),0) as special_char_ind
                    from    cte,prm
                    where   string_suffix <> ''
                    )  t

            where   special_char_ind is not null

        )

select      special_char
           ,ascii(special_char) as ascii_special_char
           ,count(*)            as cnt      

from        cte

where       special_char <> ''

group by    special_char

option      (maxrecursion 0)

+--------------+--------------------+-----+
| special_char | ascii_special_char | cnt |
+--------------+--------------------+-----+
|              |  9                 | 1   |
+--------------+--------------------+-----+
| &            | 38                 | 1   |
+--------------+--------------------+-----+
| ,            | 44                 | 1   |
+--------------+--------------------+-----+
| .            | 46                 | 5   |
+--------------+--------------------+-----+
| /            | 47                 | 1   |
+--------------+--------------------+-----+

Upvotes: 2

Related Questions