Need a function to get a list of values in a table

Posted by Kirthiga under Sql Server on 8/16/2012 | Points: 10 | Views : 1095 | Status : [Member] | Replies : 6
Hi All,

Iam having list of value like this

'CHOPL,NAKRL,NLGD2,DEVKD,HALYA'

If I execute a function i want a table like this
Number IntegerFromList
1 CHOPL
2 NAKRL
3 NLGD2
4 DEVKD
5 HALYA

Can anyone help me to write a function for this scenario




Responses

Posted by: Pandians on: 8/16/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Check It Out!
If OBJECT_ID('GetList','IF') Is Not Null

Drop Function GetList
Go
Create Function Dbo.GetList() Returns Table
as
Return
(
Select 1 Number,'CHOPL' IntegerFromList
Union
Select 2,'NAKRL'
Union
Select 3,'NLGD2'
Union
Select 4,'DEVKD'
Union
Select 5,'HALYA'
)
Go
Select * from dbo.GetList()

Go



Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kirthiga on: 8/16/2012 [Member] Starter | Points: 25

Up
0
Down
Thank You..

But in my list im having more than 500 records. This method is not possible for that much records

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 8/16/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
So... Just push all the possible data in one Table and Use that table inside the function! That will be best!

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: San.Pblr.Gct on: 8/16/2012 [Member] Starter | Points: 25

Up
0
Down
use this procedure

Create PROCEDURE asp_splitstrings

(@string varchar(50))
AS
DECLARE @id integer = 1,
@previous_id integer = 0,
@value varchar(50)

create table #temp
(Number int Identity(1,1),
IntegerFromList varchar(50))
WHILE @id > 0
BEGIN
SET @id = CHARINDEX(',',@string,@previous_id+1)
IF @id > 0
BEGIN
SET @value = SUBSTRING(@string,@previous_id+1,@id-@previous_id-1)

INSERT INTO #temp VALUES (@value)
SET @previous_id = @id
END
END
IF @previous_id < LEN(@string)
BEGIN
SET @value = SUBSTRING(@string,@previous_id+1,LEN(@string))
INSERT INTO #temp VALUES (@value)
END
select * from #temp



Execute sp now

   exec asp_splitstrings 'CHOPL,NAKRL,NLGD2,DEVKD,HALYA'


Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Srilu.Nayini577 on: 8/17/2012 [Member] Starter | Points: 25

Up
0
Down
Good response.But is it solve the problem?


SRILATHA
.Net Developer

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: San.Pblr.Gct on: 8/17/2012 [Member] Starter | Points: 25

Up
0
Down
@Srilu, check my solution and let me know whether its solving or not..

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response