使用SQL Select提取不同的特殊字符
问题描述:
我有一个Table Company。我需要知道公司名称中的特殊字符是什么。使用SQL Select提取不同的特殊字符
考虑样品表
S.No. CompanyName
__________________________________
1. 24/7 Customers
2. Rose & Co.
3. Rose Inc. Corp.
4. Rose Pvt. Ltd.,
从上面的表格,我需要选择只有鲜明的特殊字符知道什么是所有的特殊字符都参与了公司名称
的上表的输出应为/&.,
答
有一个地方,你需要确定你的 “常规” 字
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 |
+--------------+--------------------+-----+
答
试试这个,
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('%[,[email protected]\.&/]%',CompanyName),1,'') CompanyName
,SUBSTRING(CompanyName,PATINDEX('%[,[email protected]\.&/]%',CompanyName),1) spcol from @t
union ALL
select sno,
replace(CompanyName,SUBSTRING(CompanyName,PATINDEX('%[,[email protected]\.&/]%',CompanyName),1),'')
,SUBSTRING(CompanyName,PATINDEX('%[,[email protected]\.&/]%',CompanyName),1)
from cte
where PATINDEX('%[,[email protected]\.&/]%',CompanyName)>0
)
select distinct spcol
from cte
你是如何定义的特殊字符?非字母,但不是空格? – jarlh
如果条目仅用ASCII,则可以使用范围查询完成。在Unicode中可能不那么容易。 – Namphibian