--Here it goes:
----------------
-- FieldCount --
----------------
CREATE FUNCTION [dbo].[FieldCount](@S VARCHAR(8000), @Separator VARCHAR(10))
RETURNS INT
AS
BEGIN
/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/
DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Result INT
IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END
WHILE CHARINDEX(@Separator + @Separator, @S) > 0
SET @S = Replace(@S, @Separator + @Separator, @Separator + '_-_' + @Separator)
IF @S <> ''
SET @Result = 1
ELSE
BEGIN
SET @Result = 0
RETURN(@Result)
END
SET @Ptr = 0
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE @p > 0
BEGIN
SET @Result = @Result + 1
SET @Ptr = @Ptr + @p + @LenSep
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END
RETURN(@Result)
END
--------------
-- GetField --
--------------
CREATE FUNCTION [dbo].[GetField](@S VARCHAR(8000), @Separator VARCHAR(10), @Field INT)
RETURNS VARCHAR(8000)
AS
BEGIN
/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/
DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Fld INT, @Result VARCHAR(8000)
IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END
IF @Field > dbo.FieldCount(@S, @Separator)
BEGIN
SET @Result = ''
RETURN(@Result)
END
SET @Fld = 1
SET @Ptr = 1
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE (@p > 0) and (@Fld < @Field)
BEGIN
SET @Fld = @Fld + 1
SET @Ptr = @Ptr + @p + @LenSep - 1
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END
IF (@p = 0) and (@Fld = @Field)
SET @p = @LenS - @Ptr + 2
SET @Result = SUBSTRING(@S, @Ptr, @p - 1)
RETURN(@Result)
END
/* USAGE*/
select dbo.FieldCount('A1 A2 A3 A4 A5', ' ')
--It will return 5
select dbo.GetField('A1 A2 A3 A4 A5', ' ', 3)
--It will return 'A3'
select dbo.GetField('A1/A2/A3/A4/A5', '/', 3)
--It will return 'A3'
--Hope it works for you.
--Leonardo Augusto
DECLARE @FullName VARCHAR(100)
SET @FullName = 'John Doe'
SELECT SUBSTRING(@FullName, 1, CHARINDEX(' ', @FullName) - 1) AS [FirstName],
SUBSTRING(@FullName, CHARINDEX(' ', @FullName) + 1, LEN(@FullName)) AS [LastName]
Ref:
Split Name[
^]
This SQL
Split Function
is use to
SPLIT a sentences based on the Delimeter.
Delimeter is a string character used to identify substring limits.
Refer:
SQL Split Function[
^]
Submit an article or tip