SQL server tokenizer

Question

Is there a way in SQL server to create a function to have the following string 'ABC,DEF,GHI' output the value 3.

And then is it possible to have a function that would allow you to specify what token of the string you want output. So input would be the string and the token you want returned. input: ('ABC,DEF,GHI', 2) output: DEF

Welcome in non-atomic columns, comma separated string and split function. There are tons of it in SO (UDF, XML + CROSS APPLY, SUBSTRING) choose what you need — Lukasz Szozda, Nov 05 '15 at 18:43
The best possible option would be to not store delimited data in the first place. — Sean Lange, Nov 05 '15 at 19:29
Possible duplicate of [T-SQL split string](http://stackoverflow.com/questions/10914576/t-sql-split-string) — Tab Alleman, Nov 05 '15 at 19:36

score 2 · Accepted Answer · answered Nov 05 '15 at 18:43

2

The first question

declare @token varchar(20)
set @token = 'ABC,DEF,GHI'

select len(@token) - len(replace(@token ,',','')) + 1

answered Nov 05 '15 at 18:43

Juan Carlos Oropeza

47,252
12
78
118

Shnugo · Answer 2 · 2015-11-09T19:32:58.713

this is much shorter than the code you are writing:

A short explanation: By replacing the , with </x><x> we just have to add a <x>in front and a </x> at the end and - voila! - we've got XML.

XML is easy to index. You must use the XQuery-function sql:variable() to get the variable index into the XPath.

DECLARE @string VARCHAR(MAX)='ABC,DEF,GHI'; 
DECLARE @index INT=2;

WITH AsXML AS 
(
    SELECT CAST('<x>' + REPLACE(@string,',','</x><x>') + '</x>' AS XML) AS Splitted
)
SELECT Splitted.value('/x[sql:variable("@index")][1]','varchar(max)')
FROM AsXML

EDIT: Here you find a fully working example with this approach as a function.

CREATE FUNCTION dbo.Tokenizer
(
     @string VARCHAR(MAX)
    ,@index INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @RetVal VARCHAR(MAX);

    WITH AsXML AS 
    (
        SELECT CAST('<x>' + REPLACE(@string,',','</x><x>') + '</x>' AS XML) AS Splitted
    )
    SELECT @RetVal = Splitted.value('/x[sql:variable("@index")][1]','varchar(max)')
    FROM AsXML;

    RETURN @RetVal; 
END
GO

SELECT dbo.Tokenizer( 'ABC,DEF,GHI',2); --Returns "DEF"
GO

--Clean up
DROP FUNCTION dbo.Tokenizer;

@dog2bert, please look at the edits in my answer. And: Please be aware, that the professionals giving answers here, are hungry for reputation points. It would be very kind of you to vote helpful answers up and - if an answer helped you to solve your problem - you should mark it as the accepted answer, This will show to others, that this question is solved. If the answer is not helpfull please place a comment,Thx! Btw: I just pushed you over the magic 15 rep points to make you able to vote on answers. Please go through your older questions too. You did not vote or accept any of them... — Shnugo, Nov 09 '15 at 19:35

score 0 · Answer 3 · answered Nov 05 '15 at 19:57

This function does what I need for part 2

CREATE FUNCTION dbo.itemAtIndexInASplitString( @stringToSplit VARCHAR(MAX), @delimiter VARCHAR(5), @indexToReturn int)
RETURNS
varchar(max)
AS
BEGIN
DECLARE @returnList TABLE ([ID] int, [Name] [nvarchar] (500))
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
DECLARE @index INT
DECLARE @return_value varchar(max)
SET @index = 0
SET @return_value = null

WHILE CHARINDEX(@delimiter, @stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(@delimiter, @stringToSplit)  
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList 
SELECT @index, @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
SET @index = @index + 1 END

INSERT INTO @returnList
SELECT @index, @stringToSplit

SELECT @return_value = Name from @returnList where [ID] = @indexToReturn

RETURN @return_value
END
GO

Your code is working but far to complicated... If ever possible one should avoid procedural approaches in SQL. This is a set based language... That means: avoid loops (WHILE), cursors and other "step-after-step" concepts. I posted an answer for your second question. You've got one for your first already... — Shnugo, Nov 05 '15 at 20:48

SQL server tokenizer

3 Answers3