hi All ,
I am getting one param for a SP as list of states from the Front End as :
@.states = 'NY,NJ,CA,Fl,MA' . Now i have to convert this param in the form :
@.states_for_SP = 'NY','NJ','CA','Fl','MA' . Is there any efficient way to do it except using REPLACE function. As this portion in our SP is taking a lot of time in converting in the desired form.
Plz suggest to do this.
Thanks.
I don't understand why a simple replace, e.g.
Code Snippet
declare @.a varchar(100)
set @.a = '''NY,NJ,CA,Fl,MA'''
declare @.b varchar(100)
set @.b = replace(@.a, ',', ''',''')
select @.a, @.b
Gives 'NY,NJ,CA,Fl,MA' > 'NY','NJ','CA','Fl','MA'
Should be slow. Is this similar to what you're trying to do?
Greg.
|||
Mohit,
This is a slightly different approach.
Using the function below you can convert the list into a table and then join the table into your query.
Code Snippet
IFEXISTS(
SELECT*FROMsys.objects
WHEREobject_id=OBJECT_ID(N'[dbo].[list2set]')
ANDtypein(N'FN', N'IF', N'TF', N'FS', N'FT')
)
DROPFUNCTION [dbo].[list2set];
GO
CREATEFUNCTION dbo.list2set( @.list nvarchar(max), @.delim nvarchar(10))
RETURNS @.resultset TABLE( pos intidentity, item nvarchar(max))
AS
BEGIN
IFlen(@.list)<1 RETURN;
DECLARE @.xList XML;
-- no validity tests are performed, depending on input this could fail
SET @.xList =Convert(XML,''+REPLACE(@.list, @.delim,'')+'')
INSERTINTO @.resultset
SELECT data.listitem.value('.','nvarchar(max)')as item
FROM @.xList.nodes('/list/item') data(listitem)
RETURN
END
GO
You'd then use it as such:
Code Snippet
SELECT adr.state
FROM Address adr
innerjoin dbo.list2set(@.states, N',') sel
on adr.state = sel.item
|||
here the code..
Code Snippet
Create Table #Numbers(
Number Int
);
Declare @.I as int;
Set @.I = 1
While @.I<100
Begin
Insert Into #Numbers values(@.I);
Set @.I = @.I + 1;
End
Declare @.states varchar(100)
Set @.states = 'NY,NJ,CA,Fl,MA'
Declare @.StatesTable Table
(
State Varchar(100)
)
Insert Into @.StatesTable
Select Substring(',' + @.states + ',', Number, CharIndex(',',',' + @.states + ',',Number) - Number)
From
#Numbers
Where
Number<=Len(',' + @.states + ',')
And Substring(',' + @.states + ',',Number-1 ,1) = ','
--As you wise for concatination..
Set @.states = ''
Select @.states = @.states + ',''' + State + '''' From @.StatesTable
Select Substring(@.states,2,8000)
--Now You can use this @.StatesTable on any query for IN operator..
--Select * From SomeTable Where States in (Select State From @.StatesTable)
