Split strings (SQL Server 2008)

Transact-SQL
1

I have a number of string fields in a report that I need to split into their component parts. The fields can contain differing number of component parts using different delimiters, so for each fields I'd like to pass in a set of delimiters which would then split the string, e.g.
String = '0=0, 4000=7200.0'
Delimiters '=',', ','='
Split values = 0,0,4000,7200.00

or
String = 'HH= HI= LO= LL= RC= HH=1 HI=1 LO=1 LL=1 RC=1'
Delimiters = 'HH=',' HI=',' LO= ' etc.
Split values = ' , , , , ,1,1,1,1,1'

Is this possible, i.e. to have a generic split function that I can pass different delimiters in to?
Note that not all the comments parts are numeric, but the patterns for each field are predictable

Thanks

2

check out splitter

3

Using the Split String function in SQL Query

 SELECT Item
 FROM dbo.'plitString('string1,'string,....')

or you can use split string function in stored procedure. Here I am writing a sample of string function in stored procedure.

CREATE PROCEDURE GetEmployees
      @EmployeeIds VARCHAR(100)
AS
BEGIN
      SELECT FirstName, LastName
      FROM Employees
      WHERE EmployeeId IN(
            SELECT CAST(Item AS INTEGER)
            FROM dbo.SplitString(@EmployeeIds, ',')
      )
END
4

@Jason_Clark,

Just to be sure... There is no "dbo.SplitString" function in T-SQL. Please post the code for it because, if it's the one I'm thinking of, it's either got a WHILE Loop in it or is using the rather slothful "XML" method.

5

Actually, If you read OP requirement carefully, the normal standard split function does not work. Will need to cook up something new. Probably can use one of the split function as a base

6

Understood and agreed. I did previously read the requirement and I did see that there are multiple delimiters necessary. If first wanted to make sure that folks know there is no native SplitString function and also wanted to see what that function consisted of.

1 Like
7

In the first case, do a split on commas and then a sub-split on each element at the '=' sign.

In the second case, do a split on the spaces and then a sub-split on each element at the '=' sign.

The Sub-Split would be done either by another CROSS APPLY of the splitter or a CHARINDEX+1 for a SUBSTRING in either case.

8

i am using SwePeso's fnParseList & fnParseString() from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033, use the version that allows for multicharacter delimiter.

I also modify the function fnParseString() a bit to return the entire string if the delimiter is empty. This is to cater for the last section

DECLARE @NextPos SMALLINT,
	@LastPos SMALLINT,
	@Offset TINYINT = DATALENGTH(@Delimiter),
	@Found SMALLINT;

--  ## Added these 2 lines ## here 
IF @Offset = 0
	RETURN @Text

IF @Section > 0
	SELECT @Text = REVERSE(@Text),
		@Delimiter = REVERSE(@Delimiter);

the Final Query. Sorry the formatting is out. http://poorsql.com/ can't process the query :frowning:

declare    @String varchar(100) = '0=0, 4000=7200.0'

declare    @Delimiters varchar(10)    = '=|, |='    -- using Pipe as delimiter

; with delimiters as
(
    -- parse the delimiter to rows
    select    RowID, Delimiter = Data
    from    fnParseList('|', @Delimiters + '|') -- append the pipe so that it will return the last section
),
rcte as
(
    -- recursive CTE
    select    d.RowID, d.Delimiter, 
        Val     = dbo.fnParseString(-1, d.Delimiter, @String), 
        String     = stuff(@String, 1, datalength(dbo.fnParseString(-1, d.Delimiter, @String)) + datalength(d.Delimiter), '')
    from    delimiters d
    where    d.RowID    = 1

    union all

    select    d.RowID, d.Delimiter,
        Val    = dbo.fnParseString(-1, d.Delimiter, r.String),
        String     = stuff(r.String, 1, datalength(dbo.fnParseString(-1, d.Delimiter, r.String)) + datalength(d.Delimiter), '')
    from    rcte r
        inner join delimiters d        on    r.RowID = d.RowID - 1
)
select    *
from    rcte
1 Like
9

My recommendation would be to avoid any splitter code that contains a WHILE loop and/or lives in an mTVF (multi-statement Table Valued Function). You can do much the same with the splitter that Gerald cited without the performance hit. If you need a splitter that handles larger than NVARCHAR(4000) or VARCHAR(8000), I strongly recommend a good (hard to find, actually) SQLCLR splitter.