Use [Dev]
Go
/***** Object: userdefinedfunction [DBO]. [formatstr] script Date: 12/21/2011 17:32:18 ******/
Set ansi_nulls on
Go
Set quoted_identifier on
Go
Alter function [DBO]. [formatstr] (@ STR varchar (1000 ))
Returns varchar (1000)
As
Begin
-- Declare @ STR varchar (50)
Declare @ lenstr int, @ I int, @ index int
Declare @ tmpstr varchar (1000)
Declare @ lastchar bit
Set @ lastchar = 0
Set @ I = 1
-- Set @ STR = 'r11-103422, R11-103455, R11-103421'
Set @ lenstr = Len (@ Str );
Set @ tmpstr =''
Set @ Index = 0;
While (1 = 1)
Begin
If charindex (',', @ STR, 0) = 0
Begin
Set @ lastchar = 1;
Set @ tmpstr = @ tmpstr + ''' + @ STR + '''';
Set @ tmpstr = '(' + @ tmpstr + ')'
Return @ tmpstr
End
Set @ Index = charindex (',', @ STR, 0 );
-- Set @ tmpstr = substring (@ STR, 1, @ index );
Set @ lenstr = Len (@ Str );
If @ lastchar = 0
Begin
Set @ tmpstr = @ tmpstr + ''' + substring (@ STR, 1, @ index-1) + ''','
Set @ STR = substring (@ STR, @ index + 1, @ lenstr-@ index)
Set @ I = @ I + 1
End
-- Else
-- Begin
-- Set @ tmpstr = @ tmpstr + ''' + substring (@ STR, 1, @ index-1) + ''''
-- Set @ STR = substring (@ STR, @ index + 1, @ lenstr-@ index)
-- Set @ I = @ I + 1
-- Break;
-- End
End
Set @ tmpstr = '(' + @ tmpstr + ')'
Return @ tmpstr
End