DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to parse a string with complex CHARINDEX?

The following Microsoft SQL Server T-SQL code examples demonstrate parsing / splitting of a string and returning substrings.

--  T-SQL applying charindex for string parsing

USE NorthWind

DECLARE  @ProductCode VARCHAR(256)

SET @ProductCode = 'CCCC-DDDDDDD-AAA-BBBBB'

SELECT [Part1] = LEFT(@ProductCode,CHARINDEX('-',@ProductCode) - 1),

       [Part2] = SUBSTRING(@ProductCode,CHARINDEX('-',@ProductCode) + 1,

                           CHARINDEX('-',@ProductCode,CHARINDEX('-',

                           @ProductCode) + 1) - (CHARINDEX('-',@ProductCode) + 1)),

       [Part3] = SUBSTRING(@ProductCode,CHARINDEX('-',

                           @ProductCode,CHARINDEX('-',@ProductCode) + 1) + 1,

                           DATALENGTH(@ProductCode) - CHARINDEX('-',

                           @ProductCode,CHARINDEX('-',@ProductCode) + 1) -

                           CHARINDEX('-',REVERSE(@ProductCode))),

       [Part4] = RIGHT(@ProductCode,CHARINDEX('-',REVERSE(@ProductCode)) - 1)

GO

/*

Part1 Part2 Part3 Part4

CCCC  DDDDDDD     AAA   BBBBB

*/

------------

 

------------

-- SQL Server Table-Valued Function for string parsing/splitting - TVF

------------

USE AdventureWorks2008

GO

CREATE FUNCTION dbo.fnParseStringXML (

      @StringList NVARCHAR(MAX),

      @Delimiter CHAR(1))

RETURNS @TableList TABLE(ID int identity(1,1) PRIMARY KEY,

                         [Substring] NVARCHAR(max))

BEGIN

      IF @StringList = '' RETURN

      DECLARE @XML xml

      SET @XML = '<root><csv>'+replace(@StringList,@Delimiter,'</csv><csv>')+

                 '</csv></root>'

      INSERT @TableList

      SELECT rtrim(ltrim(replace(Word.value('.','nvarchar(max)'),char(10),'')))

             AS ListMember

      FROM @XML.nodes('/root/csv') AS WordList(Word)

RETURN

END -- FUNCTION

GO

 

-- Test TVF

DECLARE  @ProductCode VARCHAR(256)

SET @ProductCode = 'CCCC-DDDDDDD-AAA-BBBBB'

SELECT * FROM  dbo.fnParseStringXML  (@ProductCode, '-')

GO

/*

ID    Substring

1     CCCC

2     DDDDDDD

3     AAA

4     BBBBB

*/

------------

 

Related articles:

 

SQL Server Forums - Best split function

 

t-sql string parsing

 

http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE