Using XML to parse strings in SQL

I like finding clever ways to solve problems -- like in a previous post talking about how to parse filenames with T-SQL.

I read an article today by Divya Agrawal that used XML to parse strings in SQL and wanted to try it out in some code we use quite frequently: given a string (a list of values) and a delimiter, return the values in a table.

We have used custom functions like this for some time. Because there is no concept of a "list" in TSQL, we took a string and essentially parsed it to split the values into something we could work with. So the query:

select * from dbo.fn_varCharListToTable('my,name,is,bill',',')

would return

autoid listitem
1 my
2 name
3 is
4 bill

and you could then join that function's result however you needed.

The custom function you need to do this is very small when you use XML to parse the string:

ALTER Function [dbo].[fn_varCharListToTable] (@varcharlist varchar(max), @delimiter varchar(1)) 
returns @myTable table (autoid int IDENTITY (1,1), listitem varchar(max))
AS
begin

 declare @strAsXML as xml
 set @strAsXML = cast(('<x>'+replace(@varcharlist,@delimiter,'</x><x>')+'</x>') as xml)
 
 insert into @myTable(listitem)
 select N.value('.','varchar(max)') as value
 from @strAsXML.nodes('x') as T(N)

 return
end

Parsing filenames with only T-SQL

So from time to time, I get to use SQL functions in a perhaps non-conventional way. Here is one that I love: PARSENAME().

Say I have a column that contains a full path to a file on the filesystem:

ID FilePath
1 MyPath\MyFile.doc
2 MyPath\MyOtherFile.pdf
3 MyPath\MyThirdFile.xls

Let's say I need to know the file extension for those files. Using PARSENAME() will give them to me!

SELECT ID, PARSENAME(filePath,1) as fileExt
FROM myFiles

PARSENAME() will only work on strings with no more than 5 parts -- each part is a substring delimited by a period. The index (1 in the above query) takes the 1st substring starting from the right. You can use PARSENAME() to parse all sorts of things:

File Extensions: PARSENAME('myFile.doc',1) = doc
File Names: PARSENAME('myFile.doc',2) = myFile
Volume Numbers: PARSENAME('7.1.34',3) = 7
Database Objects: PARSENAME('myDb.dbo.myTable.myColumn',2) = myTable

May come in handy for you at some point.