Friday, March 8, 2013

split strings into rows of characters




ALTER FUNCTION [dbo].[fn_CharSplit4K](
@pString NVARCHAR(4000)
)
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
-- From the article: http://www.sqlservercentral.com/articles/Tally+Table/72993/
RETURNS TABLE WITH SCHEMABINDING AS
RETURN

select
row_number() over(order by tl.N) as ItemNumber
,substring(@pString, tl.N, 1) as Item
from
dbo.TallyLarge tl with(nolock)
where
tl.N <= len(@pString)

No comments: