If you need something like Split('ABC - DE ') and would like to get
1) 'ABC' --> The first part of the string
or
2) 'DE' --->The second part of the string
2) I will start by the one I created:
Here is how to
get the second part of the string:
CREATE FUNCTION [dbo].[GetSecondString] (@text VARCHAR(MAX), @character varchar(64))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @start int;
DECLARE @result VARCHAR(MAX)
SELECT @start = CHARINDEX(@character,@text) --Get the starting character
---Substring(text, start right after the character, length of text (second string))
SELECT @result = LTRIM(RTRIM(substring(@text, @Start +1, LEN(@text)-@start)))
return @result
END
--------------------
Executing the function
SELECT dbo.GetSecondString ('abcdef1234- What a great result','-') AS myResult;
will produce -->"What a great result"
--------------------
1) To get the first part -obtained from the
SQl Authority
CREATE FUNCTION [dbo].[GetFirst] (@StringVar VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @rtStringVar VARCHAR(MAX)
SELECT @rtStringVar = CASE CHARINDEX('-', LTRIM(@StringVar), 1)
WHEN 0 THEN LTRIM(@StringVar)
ELSE SUBSTRING(LTRIM(@StringVar), 1, CHARINDEX('-',LTRIM(@StringVar), 1) - 1)
END
RETURN @rtStringVar
END
---------------------
Executing the function
-Usage of FunctionSELECT dbo.GetFirstWord ('') AS FirstWordofSentence;SELECT dbo.GetFirstWord (' Space Before') AS FirstWordofSentence;SELECT dbo.GetFirstWord ('Long Statement') AS FirstWordofSentence;SELECT dbo.GetFirstWord ('SingleWord') AS FirstWordofSentence;