Я знаю MS, T-SQL не поддерживает регулярное выражение, но мне нужна схожая функциональность. Вот то, что я пытаюсь сделать:
У меня есть varchar поле таблицы, которое хранит навигационную цепочку, как это:
/ID1:Category1/ID2:Category2/ID3:Category3/
Каждому Названию категории предшествует его идентификатор Категории, разделенный двоеточием. Я хотел бы выбрать и отобразить эти навигационные цепочки, но я хочу удалить идентификаторы Категории и двоеточия, как это:
/Category1/Category2/Category3/
Все между ведущей наклонной чертой (/) до и включая двоеточие (:) должен быть разделен.
У меня нет опции извлечения данных, управления им внешне и перевставки назад в таблицу; таким образом, я пытаюсь выполнить это в операторе SELECT.
Я также не могу обратиться к использованию курсора, чтобы циклично выполниться через каждую строку и убрать каждое поле с вложенным циклом, из-за количества строк, возвращенных в ВЫБОРЕ.
Это может быть сделано?
Спасибо все - Сойка
Вы можете сделать это с помощью функции разделения. Следующая функция разделения полагается на существование таблицы чисел, которая буквально содержит последовательный список чисел, например:
Create Table dbo.Numbers( Value int not null primary key clustered )
GO
With Nums As
(
Select ROW_NUMBER() OVER( Order By o.object_id ) As Num
From sys.objects as o
cross join sys.objects as o2
)
Insert dbo.Numbers( Value )
Select Num
From Nums
Where Num Between 1 And 10000
GO
Create Function [dbo].[udf_Split] (@DelimitedList nvarchar(max), @Delimiter nvarchar(2) = ',')
Returns @SplitResults TABLE (Position int NOT NULL PRIMARY KEY, Value nvarchar(max))
AS
/*
PURPOSE: to split the @DelimitedList based on the @Delimter
DESIGN NOTES:
1. In general the contents of the next item is: NextDelimiterPosition - CurrentStartPosition
2. CurrentStartPosition =
CharIndex(@Delimiter, A.list, N.Value) = Current Delimiter position
+ Len(@Delimiter) + The number of delimiter characters
+ 1 + 1 since the text of the item starts after the delimiter
3. We need to calculate the delimiter length because the LEN function excludes trailing spaces. Thus
if a delimiter of ", " (a comma followed by a space) is used, the LEN function will return 1.
4. The DataLength function returns the number of bytes in the string. However, since we're using
an nvarchar for the delimiter, the number of bytes will double the number of characters.
*/
Begin
Declare @DelimiterLength int
Set @DelimiterLength = DataLength(@Delimiter) / 2
If Left(@DelimitedList, @DelimiterLength) <> @Delimiter
Set @DelimitedList = @Delimiter + @DelimitedList
If Right(@DelimitedList, @DelimiterLength) <> @Delimiter
Set @DelimitedList = @DelimitedList + @Delimiter
Insert @SplitResults(Position, Value)
Select CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength
, Substring (
A.List
, CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength
, CharIndex(@Delimiter, A.list, N.Value + 1)
- ( CharIndex(@Delimiter, A.list, N.Value) + @DelimiterLength )
)
From dbo.Numbers As N
Cross Join (Select @DelimitedList As list) As A
Where N.Value > 0
And N.Value < LEN(A.list)
And Substring(A.list, N.Value, @DelimiterLength) = @Delimiter
Order By N.Value
Return
End
Затем вы можете выполнить такой запрос, в котором вы удаляете префиксы:
Select Table, Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
From Table
Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S
Это даст вы можете использовать такие значения, как:
Category1
Category2
Category3
Затем вы можете использовать FOR XML PATH
, чтобы снова объединить их:
Select Table.PK
, Stuff( (
Select '/' + Substring(S.Value, CharIndex(':', S.Value) + 1, Len(S.Value))
From Table As Table1
Cross Apply dbo.udf_Split(Table.ListColumn, '/') As S1
Where Table1.PK = Table.PK
Order By S1.Position
For Xml Path('')
), 1, 1, '') As BreadCrumb
From Table
Это работает для SQL Server 2005 и выше.
create table strings (
string varchar(1000)
)
insert into strings values( '/ID1:Category1/ID2:Category2/ID3:Category3/' )
insert into strings values( '/ID4:Category4/ID5:Category5/ID8:Category6/' )
insert into strings values( '/ID7:Category7/ID8:Category8/ID9:Category9/' )
go
with
replace_with_wildcard ( restrung ) as
(
select replace( string, '', '' )
from strings
union all
select
replace( restrung, substring( restrung, patindex( '%ID%', restrung ), 4 ), '' )
from replace_with_wildcard
where patindex( '%ID%', restrung ) > 0
)
select restrung
from replace_with_wildcard
where charindex( ':', restrung ) = 0
order by restrung
drop table strings
Для SQL Server 2005+ поддержку regex можно получить следующим образом:
Используя родной TSQL, вам нужно будет определить операторы REPLACE для всего, что вы хотите удалить:
SELECT REPLACE(
REPLACE(
REPLACE(''/ID1:Category1/ID2:Category2/ID3:Category3/'', 'ID1:', ''),
'ID2:', ''),
'ID3:', '')
Regex или иначе, вы должны быть уверены, что эти шаблоны не появятся в фактических данных.
Я думаю, что лучше всего вам будет использовать рекурсивную функцию, определяемую пользователем (UDF). Я включил сюда некоторый код, который можно использовать для передачи строки, чтобы достичь результатов, которые вы ищете.
CREATE FUNCTION ufn_StripIDsFromBreadcrumb (@cIndex int, @breadcrumb varchar(max), @theString varchar(max))
RETURNS varchar(max)
AS
BEGIN
DECLARE @nextColon int
DECLARE @nextSlash int
SET @nextColon = CHARINDEX(':', @theString, @cIndex)
SET @nextSlash = CHARINDEX('/', @theString, @nextColon)
SET @breadcrumb = @breadcrumb + SUBSTRING(@theString, @nextColon + 1, @nextSlash - @nextColon)
IF @nextSlash != LEN(@theString)
BEGIN
exec @breadcrumb = ufn_StripIDsFromBreadcrumb @cIndex = @nextSlash, @breadcrumb = @breadcrumb, @theString = @theString
END
RETURN @breadcrumb
END
Затем вы можете выполнить его с помощью:
DECLARE @myString varchar(max)
EXEC @myString = ufn_StripIDsFromBreadcrumb 1, '/', '/ID1:Category1/ID2:Category2/ID3:Category3/'
PRINT @myString