By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Substring in SQL server

P: 3
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
Sep 15 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
Sounds like an assignment :)

What do you have so far?

--- CK
Sep 16 '09 #2

P: 3
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.
Sep 16 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.