Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012
Posts: 106
Points: 327
Location: Tbilisi
Thanks: 0 times
Was thanked: 0 time(s) in 0 post(s)
|
ამ ოპერაციის შესასრულებლად დაგჭირდებათ Inline Table-valued Function - [isRows], რომელიც გამოიყურება შემდეგნაირად: BB კოდი:SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
-- ===========================================================================================
-- Inline Table-valued Function
-- GETS DATA FROM VIRTUAL TABLE
-- GENERATED BY : APEX GROUP
-- GENERATED ON :20080607
-- ===========================================================================================
/*-- SAMPLE EXECUTION ========================================================================
-- GET ---------------------------------------------------------------------------------------
-- აბრუნებს ნატურალური რიცხვების სიას @nMin და @nMax შუალედში
SELECT nrow
FROM dbo.isRows(1, 7) AS a
-- SAMPLE EXECUTION ========================================================================*/
ALTER FUNCTION [dbo].[isRows]
(
@nMin int = 1,
@nMax int = 1
)
RETURNS TABLE
AS
RETURN
(
WITH cte(nMin, nMax) AS (SELECT @nMin AS nMin, @nMax AS nMax
UNION ALL
SELECT nMin + 1 AS nMin, nMax
FROM cte
WHERE (nMin < nMax))
SELECT nMin AS nrow
FROM cte
) ან შეგიძლიათ იგივე გააკეთოთ, ფუნქციის გამოუყენებლადაც : BB კოდი:DECLARE @input nvarchar(100), @output nvarchar(100)
SET @input = N'1 23 A->B->C<-D=4567 SAMPLE FROM VK 89 -+0';
WITH cte(nMin, nMax) AS (SELECT 1 AS nMin, 100 AS nMax
UNION ALL
SELECT nMin + 1 AS nMin, nMax
FROM cte
WHERE (nMin < nMax)), ctisRows AS
(SELECT nMin AS nrow
FROM cte), ct1 AS
(SELECT nrow AS Number, SUBSTRING(@input, nrow, 1) AS Val
FROM dbo.isRows(1, 100) AS a
WHERE (nrow <= LEN(@input)))
SELECT @output = isnull(@output,'') + Val
FROM ct1
WHERE (Val LIKE N'[0-9]')
SELECT @input as Text, @output as Numbers -- რეზულტატი: @input = N'1 23 A->B->C<-D=4567 SAMPLE FROM VK 89 -+0', @output = '1234567890'
|
