Substring in SQL server

Hi Folks...

I have a string in the format '1.2.3.4.5.6'. I have to write a generic function that will take integer as paramater and will return the string as follows for the above input.

• ParamaterValue : Output.
• 1 : 1
• 2 : 1.2
• 3 : 1.2.3
• 4 : 1.2.3.4

re: Substring in SQL server

Sounds like an assignment :)

What do you have so far?

--- CK

re: Substring in SQL server

Its not an assignment.I have come up with the below solution.

CREATE FUNCTION [dbo].[StringToTable]
(
@inputString nvarchar(max),
@separator char (1),
@tokens int
)
RETURNS nvarchar(4000)--@ResultTable TABLE ( [String] nvarchar(max) )
AS
BEGIN
DECLARE @ResultTable TABLE ( [String] nvarchar(max) )
DECLARE @ResultVar nvarchar(4000)
DECLARE @stringToInsert nvarchar (max)
DECLARE @cnt int

WHILE LEN(@inputString) > 0
BEGIN
SET @StringToInsert = LEFT(
@inputString,
ISNULL(NULLIF(CHARINDEX(@separator, @inputString) - 1, -1),
LEN(@inputString)
)
)
SET @InputString = SUBSTRING(@InputString,

ISNULL
(NULLIF
(CHARINDEX(@separator, @InputString),
0),
LEN(@InputString)) + 1,
LEN(@InputString))

INSERT INTO @ResultTable
(
[String]
)
VALUES
(
@StringToInsert
)

END

SELECT @cnt = count(*) from @ResultTable

If @cnt>= @tokens
BEGIN
SELECT @ResultVar = STUFF((SELECT top (@tokens) '.' + [String] FROM @ResultTable
FOR XML PATH('')),1, 1, '') ;
END
ELSE
BEGIN
SELECT @ResultVar = NULL
END
RETURN @ResultVar;
END

----------------------
You can execute the above function as
select [dbo].[StringToTable]('1.1.2.3','.',2) will return '1.1'

I am trying to modify the function without the use of @ResultTable TABLE
Paramater.