THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Tokenize UDF

This blog has moved! You can find this content at the following new location:

http://dataeducation.com/tokenize-udf/

Published Thursday, March 10, 2005 8:28 AM by Adam Machanic

Comments

 

Jeremy Swartwood said:

Thank you for this.  To note, if there is a token and nothing but a space, your script excludes this extra "token".  In my situation I needed to always compare a specific token number so I needed this empty token.

These changes are not efficient, but they worked.

I changed the INSERT section to use a CASE instead that compared against '' and then it didn't use the LTREM/RTRIM else it used the trim.

case when RTRIM(LTRIM(SUBSTRING(@Input, StartChar, CharNum - StartChar + 1))) = ''

then SUBSTRING(@Input, StartChar, CharNum - StartChar + 1)

 else RTRIM(LTRIM(SUBSTRING(@Input, StartChar, CharNum - StartChar + 1)))

end

Additionally, I had to change the WHERE clause because SQL thinks that '' = ' '.

SUBSTRING(@Input, StartChar, CharNum - StartChar + 1) NOT LIKE ''

AND

RTRIM(LTRIM(SUBSTRING(@Input, StartChar, CharNum - StartChar + 1))) NOT LIKE ','

May 29, 2013 4:39 PM
New Comments to this post are disabled

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.
Privacy Statement