TA貢獻1772條經驗 獲得超5個贊
CREATE FUNCTION [dbo].[fn_split_string_to_column] ( @string NVARCHAR(MAX), @delimiter CHAR(1) )RETURNS @out_put TABLE ( [column_id] INT IDENTITY(1, 1) NOT NULL, [value] NVARCHAR(MAX) )ASBEGIN DECLARE @value NVARCHAR(MAX), @pos INT = 0, @len INT = 0 SET @string = CASE WHEN RIGHT(@string, 1) != @delimiter THEN @string + @delimiter ELSE @string END WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0 BEGIN SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos SET @value = SUBSTRING(@string, @pos, @len) INSERT INTO @out_put ([value]) SELECT LTRIM(RTRIM(@value)) AS [column] SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1 END RETURNEND
TA貢獻1829條經驗 獲得超7個贊
您的目的可以使用以下查詢解決 -
Select Value , Substring(FullName, 1,Charindex(',', FullName)-1) as Name,
Substring(FullName, Charindex(',', FullName)+1, LEN(FullName)) as Surname
from Table1
在sql server中沒有現成的Split函數，所以我們需要創建用戶定義的函數。
CREATE FUNCTION Split (
@InputString VARCHAR(8000),
@Delimiter VARCHAR(50)
)
RETURNS @Items TABLE (
Item VARCHAR(8000)
)
AS
BEGIN
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END
IF (@Delimiter IS NULL OR @Delimiter = '')
SET @Delimiter = ','
--INSERT INTO @Items VALUES (@Delimiter) -- Diagnostic
--INSERT INTO @Items VALUES (@InputString) -- Diagnostic
DECLARE @Item VARCHAR(8000)
DECLARE @ItemList VARCHAR(8000)
DECLARE @DelimIndex INT
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT INTO @Items VALUES (@Item)
-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE
IF @Item IS NOT NULL -- At least one delimiter was encountered in @InputString
BEGIN
SET @Item = @ItemList
INSERT INTO @Items VALUES (@Item)
END
-- No delimiters were encountered in @InputString, so just return @InputString
ELSE INSERT INTO @Items VALUES (@InputString)
RETURN
END -- End Function
GO
---- Set Permissions
--GRANT SELECT ON Split TO UserRole1
--GRANT SELECT ON Split TO UserRole2
--GO
舉報