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 /&.,
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 |
+--------------+--------------------+-----+
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
