sqlrx logo
SQLRx Tip of the Month
 
 
April 2010
Greetings!
 
Spring is here, so we're going to share a time saving tip that'll help you finish your work and get outside faster.  And don't forget to regularly monitor the 11 VitalSigns used by SQLRx to debug performance bottlenecks.
 
______________________________________________________________________________
wrench  SQL Server Development:  Take apart delimited strings with ease using a function
  commonly found in Visual Basic.  Pass a delimited string into the fnSplit function and
  it will output the string as a table that can be queried.
 

                     

The function is called by this statement: 
 

SELECT * FROM dbo.fnSplit('SQLRx,Solves,Performance,Problems',',')

And outputs a table:

 SQLRx table

 
 
 
 
 
 
 
 
CREATEFUNCTION [dbo].[fnSplit] (@String VARCHAR(8000), @Delimiter CHAR(1))       

      RETURNS @temptable TABLE (items VARCHAR(8000))         

      AS         

      BEGIN             

            DECLARE @idx INT              

            DECLARE @slice VARCHAR(8000)  

                       

            SELECT @idx = 1                  

                  IF LEN(@String)<1 OR @String IS NULL  RETURN 

                             

            WHILE @idx!= 0             

            BEGIN                 

                  SET @idx = CHARINDEX(@Delimiter,@String)                 

                  IF @idx!=0                     

                        SET @slice = LEFT(@String,@idx - 1)                 

                  ELSE                    

                        SET @slice = @String

                                        

                  IF(LEN(@slice)>0)                

                        INSERT INTO @temptable(items) VALUES(@slice)

                                        

                  SET @String = RIGHT(@String,LEN(@String) - @idx)                 

                  IF LEN(@String) = 0 BREAK             

            END     

      RETURN         

      END

GO

______________________________________________________________________ 
SQLRx is now a part of www.msdev.com, Microsoft's training site for solution providers!  Check the site periodically to view new training videos.
 
If you have a SQL question you'd like us to answer, and possibly use for the next SQLRx Tip of the Month or msdev video, email it to us!  If you missed any of our previous tips, you can view them here.
 
For assistance or more information on optimizing your SQL Server Environment, visit us at www.sqlrx.com or email Lori Brown at [email protected].  

Sincerely,

Lori Brown | SQLRx Senior Consultant | Integrated Services, Inc.
4144 N. Central Expwy, Suite 430  |  Dallas, TX  75204
Phone: 214.526.7680 x 113 | [email protected] 
 
 
Integrated Services, Inc.                                    
2008 winner logo small
Turning Data Into Profit
 
25 Years of Credibility & Trust
 

Join our email list          Check out our events          SQLRx Downloads