SQL Tokenizer

Sometimes you need to be able to pass a delimited string to a stored procedure and then have the procedure break that string into individual pieces (tokens) and process each.

A good example would be zip codes. You have a stored procedure that will take a zip code and return given information about it. Works great but now you need to be able to pass in a number of zip codes and then return the information on all of them in one report.

Lots of ways to do it, but I found this to be kind of handy. This code will take a delimited string and create a temp table of tokens that can then be processed in a loop. So if you pass in "12345,23456,34567,45678" it will create a temp table that looks like this...

12345
23456
34567
45678

and then you can process each entry. It can easily be turned into a stored procedure or function or just added to existing code.

Please feel free to use and modify this code as you need. As always this code is presented "as is" and you use at your own risk.

DECLARE
@TolkenString VARCHAR(500),
@i INT,
@iLenght INT,
@Tolken VARCHAR(500),
@Delimiter CHAR(1),
@Character VARCHAR(500)

SET @TolkenString = 'The brown Fox'

SET @iLenght = LEN(@TolkenString)
SET @i = 1
SET @Delimiter = ' '
SET @Tolken = ''

CREATE TABLE #TempTolken
(
Tolken VARCHAR(500)
)


--select @iLenght

While @i < = @iLenght BEGIN SELECT @Character = substring(@TolkenString,@i,1) IF @Character = @Delimiter BEGIN INSERT #TempTolken Values(@Tolken)
SET @Tolken = ''
END
ELSE
BEGIN
SET @Tolken = @Tolken + substring(@TolkenString,@i,1)
--SELECT @Tolken
END

SET @i = @i + 1

END

INSERT #TempTolken Values(@Tolken)
SET @Tolken = ''

SELECT * FROM #TempTolken

DROP TABLE #TempTolken