SQL: как получить все отличные символы в столбце через все строки
Существует ли изящный путь в SQL Server для нахождения всех отличных символов в единственном varchar (50) столбец через все строки?
Бонусные очки, если это может быть сделано без курсоров :)
Например, скажите, что мои данные содержат 3 строки:
productname
-----------
product1
widget2
nicknack3
Отличные материально-технические ресурсы символов были бы "productwigenka123"
4 ответа
Учитывая, что ваш столбец - 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('');
Если у вас есть таблица Numbers или Tally, содержащая последовательный список целых чисел, вы можете сделать что-то вроде:
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('')
Если вы используете SQL Server 2005 и более позрустённые версии, вы можете создать таблицу Numbers на лету с помощью 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('')
Вот запрос, который возвращает каждый символ в виде отдельной строки вместе с количеством вхождений. Предполагая, что ваша таблица называется «Продукты»
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
Чтобы объединить их все в одну строку (как указано в вопросе), измените окончательный SELECT на
SELECT aChar AS [text()] FROM
(SELECT DISTINCT aChar FROM ProductChars) base
FOR XML PATH('')
Выше используется хороший хак, который я нашел here, который эмулирует GROUP_CONCAT из MySQL.
Первый уровень рекурсии разворачивается, чтобы запрос не возвращал пустые строки в выходных данных.
Используйте это (должно работать на любой РСУБД с поддержкой CTE):
select x.v into prod from (values('product1'),('widget2'),('nicknack3')) as x(v);
Тестовый запрос:
with a as
(
select v, '' as x, 0 as n from prod
union all
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
option (maxrecursion 0)
Окончательный запрос:
with a as
(
select v, '' as x, 0 as n from prod
union all
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
option (maxrecursion 0)
Версия 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