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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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