Create a user-defined function, which is a stored Transact-SQL routine of the returned value. User-defined functions cannot be used to modify the global database status. Like system functions, user-defined functions can be called from queries. It can also be executed through the EXECUTE statement like a stored procedure
Create a user-defined function, which is a stored Transact-SQL routine of the returned value. User-defined functions cannot be used to modify the global database status. Like system functions, user-defined functions can be called from queries. It can also be executed through the EXECUTE statement like a stored procedure
The Code is as follows:
Create function fGetStrBySplit
(
@ Source VARCHAR (max ),
@ Index INT,
@ SplitChar VARCHAR (1)
)
RETURNS varchar (MAX)
AS
BEGIN
DECLARE @ Len INT
DECLARE @ n INT = 0
DECLARE @ ChIndex INT
DECLARE @ Result VARCHAR (MAX)
-- Get the total length
SET @ Len = LEN (@ Source)
-- Get the position of the cut character
SET @ ChIndex = CHARINDEX (@ SplitChar, @ Source)
WHILE @ ChIndex> 0
BEGIN
IF (@ n = @ Index)
BEGIN
SET @ Source = SUBSTRING (@ Source, 0, @ ChIndex)
BREAK
END
SET @ Source = SUBSTRING (@ Source, @ ChIndex + 1, @ Len)
SET @ ChIndex = CHARINDEX (@ SplitChar, @ Source)
SET @ Len = LEN (@ Source)
SET @ n = @ n + 1
END
RETURN @ Source
END
GO
-- Call
DECLARE @ value VARCHAR (max)
SET @ value = dbo. fGetStrBySplit ('2017*01_45854_183677_12 ', 0 ,'_')
PRINT @ value
Result:
645002*01
-- 1
45854
-- 2
183677