Is there an elegant way in SQL Server to find all the distinct characters in a single varchar(50) column, across all rows?
SQL Server中是否有一种优雅的方法可以跨所有行在一个varchar(50)列中找到所有不同的字符?
Bonus points if it can be done without cursors :)
如果可以不使用游标,可以加分:)
For example, say my data contains 3 rows:
例如,假设我的数据包含3行:
productname
-----------
product1
widget2
nicknack3
The distinct inventory of characters would be "productwigenka123"
不同的字符目录是“productwigenka123”
4 个解决方案
#1
4
Given that your column is varchar, it means it can only store characters from codes 0 to 255, on whatever code page you have. If you only use the 32-128 ASCII code range, then you can simply see if you have any of the characters 32-128, one by one. The following query does that, looking in sys.objects.name:
假设您的列是varchar,这意味着它只能在您拥有的任何代码页上存储从0到255的字符。如果您只使用32-128的ASCII码范围,那么您可以看到是否有一个32-128的字符。下面的查询可以做到这一点,请参阅sys.objects.name:
with cteDigits as (
select 0 as Number
union all select 1 as Number
union all select 2 as Number
union all select 3 as Number
union all select 4 as Number
union all select 5 as Number
union all select 6 as Number
union all select 7 as Number
union all select 8 as Number
union all select 9 as Number)
, cteNumbers as (
select U.Number + T.Number*10 + H.Number*100 as Number
from cteDigits U
cross join cteDigits T
cross join cteDigits H)
, cteChars as (
select CHAR(Number) as Char
from cteNumbers
where Number between 32 and 128)
select cteChars.Char as [*]
from cteChars
cross apply (
select top(1) *
from sys.objects
where CHARINDEX(cteChars.Char, name, 0) > 0) as o
for xml path('');
#2
18
Here's a query that returns each character as a separate row, along with the number of occurrences. Assuming your table is called 'Products'
这里有一个查询,它将每个字符作为单独的行返回,并返回出现的次数。假设您的表被称为“产品”
WITH ProductChars(aChar, remain) AS (
SELECT LEFT(productName,1), RIGHT(productName, LEN(productName)-1)
FROM Products WHERE LEN(productName)>0
UNION ALL
SELECT LEFT(remain,1), RIGHT(remain, LEN(remain)-1) FROM ProductChars
WHERE LEN(remain)>0
)
SELECT aChar, COUNT(*) FROM ProductChars
GROUP BY aChar
To combine them all to a single row, (as stated in the question), change the final SELECT
to
若要将它们合并为一行(如问题中所述),请将最终选择改为
SELECT aChar AS [text()] FROM
(SELECT DISTINCT aChar FROM ProductChars) base
FOR XML PATH('')
The above uses a nice hack I found here, which emulates the GROUP_CONCAT
from MySQL.
上面使用了我在这里找到的一个不错的hack,它模拟了来自MySQL的GROUP_CONCAT。
The first level of recursion is unrolled so that the query doesn't return empty strings in the output.
递归的第一个层次是展开的,这样查询就不会在输出中返回空字符串。
#3
4
Use this (shall work on any CTE-capable RDBMS):
使用这个(应该适用于任何支持cte的RDBMS):
create table prod as
select x.v from (values('product1'),('widget2'),('nicknack3')) as x(v);
Test Query:
测试查询:
with a as
(
select v, '' as x, 0 as n from prod
union
select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select v, x, n from a -- where n > 0
order by v, n
Final Query:
最后的查询:
with a as
(
select v, '' as x, 0 as n from prod
union
select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select distinct x from a where n > 0
order by x
Oracle version:
Oracle版本:
with a(v,x,n) as
(
select v, '' as x, 0 as n from prod
union all
select v, substr(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select distinct x from a where n > 0
#4
1
If you have a Numbers or Tally table which contains a sequential list of integers you can do something like:
如果你有一个包含连续整数列表的数字或计数表,你可以这样做:
Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From dbo.Numbers As N
Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')
If you are using SQL Server 2005 and beyond, you can generate your Numbers table on the fly using a CTE:
如果您正在使用SQL Server 2005或以上版本,您可以使用CTE动态生成数字表:
With Numbers As
(
Select Row_Number() Over ( Order By c1.object_id ) As Value
From sys.columns As c1
Cross Join sys.columns As c2
)
Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From Numbers As N
Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')
#1
4
Given that your column is varchar, it means it can only store characters from codes 0 to 255, on whatever code page you have. If you only use the 32-128 ASCII code range, then you can simply see if you have any of the characters 32-128, one by one. The following query does that, looking in sys.objects.name:
假设您的列是varchar,这意味着它只能在您拥有的任何代码页上存储从0到255的字符。如果您只使用32-128的ASCII码范围,那么您可以看到是否有一个32-128的字符。下面的查询可以做到这一点,请参阅sys.objects.name:
with cteDigits as (
select 0 as Number
union all select 1 as Number
union all select 2 as Number
union all select 3 as Number
union all select 4 as Number
union all select 5 as Number
union all select 6 as Number
union all select 7 as Number
union all select 8 as Number
union all select 9 as Number)
, cteNumbers as (
select U.Number + T.Number*10 + H.Number*100 as Number
from cteDigits U
cross join cteDigits T
cross join cteDigits H)
, cteChars as (
select CHAR(Number) as Char
from cteNumbers
where Number between 32 and 128)
select cteChars.Char as [*]
from cteChars
cross apply (
select top(1) *
from sys.objects
where CHARINDEX(cteChars.Char, name, 0) > 0) as o
for xml path('');
#2
18
Here's a query that returns each character as a separate row, along with the number of occurrences. Assuming your table is called 'Products'
这里有一个查询,它将每个字符作为单独的行返回,并返回出现的次数。假设您的表被称为“产品”
WITH ProductChars(aChar, remain) AS (
SELECT LEFT(productName,1), RIGHT(productName, LEN(productName)-1)
FROM Products WHERE LEN(productName)>0
UNION ALL
SELECT LEFT(remain,1), RIGHT(remain, LEN(remain)-1) FROM ProductChars
WHERE LEN(remain)>0
)
SELECT aChar, COUNT(*) FROM ProductChars
GROUP BY aChar
To combine them all to a single row, (as stated in the question), change the final SELECT
to
若要将它们合并为一行(如问题中所述),请将最终选择改为
SELECT aChar AS [text()] FROM
(SELECT DISTINCT aChar FROM ProductChars) base
FOR XML PATH('')
The above uses a nice hack I found here, which emulates the GROUP_CONCAT
from MySQL.
上面使用了我在这里找到的一个不错的hack,它模拟了来自MySQL的GROUP_CONCAT。
The first level of recursion is unrolled so that the query doesn't return empty strings in the output.
递归的第一个层次是展开的,这样查询就不会在输出中返回空字符串。
#3
4
Use this (shall work on any CTE-capable RDBMS):
使用这个(应该适用于任何支持cte的RDBMS):
create table prod as
select x.v from (values('product1'),('widget2'),('nicknack3')) as x(v);
Test Query:
测试查询:
with a as
(
select v, '' as x, 0 as n from prod
union
select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select v, x, n from a -- where n > 0
order by v, n
Final Query:
最后的查询:
with a as
(
select v, '' as x, 0 as n from prod
union
select v, substring(v,n+1,1) as x, n+1 as n from a where n < len(v)
)
select distinct x from a where n > 0
order by x
Oracle version:
Oracle版本:
with a(v,x,n) as
(
select v, '' as x, 0 as n from prod
union all
select v, substr(v,n+1,1) as x, n+1 as n from a where n < length(v)
)
select distinct x from a where n > 0
#4
1
If you have a Numbers or Tally table which contains a sequential list of integers you can do something like:
如果你有一个包含连续整数列表的数字或计数表,你可以这样做:
Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From dbo.Numbers As N
Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')
If you are using SQL Server 2005 and beyond, you can generate your Numbers table on the fly using a CTE:
如果您正在使用SQL Server 2005或以上版本,您可以使用CTE动态生成数字表:
With Numbers As
(
Select Row_Number() Over ( Order By c1.object_id ) As Value
From sys.columns As c1
Cross Join sys.columns As c2
)
Select Distinct '' + Substring(Products.ProductName, N.Value, 1)
From Numbers As N
Cross Join dbo.Products
Where N.Value <= Len(Products.ProductName)
For Xml Path('')