larsbloch
1/30/2018 - 11:51 AM

Split string without functions

This is a way to split a string without using a function. This is usefull for longer script generation

SplitString
content_copyfile_download
--Declare out input string
DECLARE @Databases nvarchar(1000)
SET @Databases = 'DSA,EDW,DM,'

--Declare XML stuff based on inputstring
DECLARE @xml xml SET @xml = '<split><el>' + replace(@Databases,',','</el><el>') + '</el></split>'

--Declare our output database string
DECLARE @DatabaseName nvarchar(200) SET @DatabaseName = ''

--Get 1 position in string
SET @DatabaseName = LTRIM(RTRIM((select el = split.el.value('.','varchar(max)') from  @xml.nodes('/split/el[string-length(.)>0][position() = 1]') split(el))))
SELECT @DatabaseName
--Get 2 position in string
SET @DatabaseName = LTRIM(RTRIM((select el = split.el.value('.','varchar(max)') from  @xml.nodes('/split/el[string-length(.)>0][position() = 2]') split(el))))
SELECT @DatabaseName
--Get 3 position in string
SET @DatabaseName = LTRIM(RTRIM((select el = split.el.value('.','varchar(max)') from  @xml.nodes('/split/el[string-length(.)>0][position() = 3]') split(el))))
SELECT @DatabaseName

--Remember to use LTRIM AND RTRIM to remove whitespaces if someone puts spaces between the data and the commas