datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
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