InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:14:02 GMT20http://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspx[quote][b]Stamey (2/16/2011)[/b][hr]That's great. I'd still like to learn how to deal with spaces for my case, as educational now, more than functional. I will probably be happier using what you have already developed for delimiter splitting.Thanks,Chris[/quote]It's easy. In T-SQL, Spaces are no different than any other character except...1. '' = '{one space}' = '{virtually any number of spaces}' for comparison purposes.2. Trailing spaces are not recognized by LEN but are recognized by DATALENGTH.Wed, 16 Feb 2011 09:18:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxThat's great. I'd still like to learn how to deal with spaces for my case, as educational now, more than functional. I will probably be happier using what you have already developed for delimiter splitting.Thanks,ChrisWed, 16 Feb 2011 08:00:10 GMTStameyhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxOh yeah... I forgot to mention it... it handles a space as a delimiter, as well.Mon, 14 Feb 2011 18:56:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxHere's my latest splitter. I'm still testing it to be included in the rewrite of the "Tally Table" article but it blows the doors off the old splitter especially when you get over 1,000 bytes. I haven't YET tested it for any of the MAX datatypes but I will tell you this... as soon as you change from (say) VARCHAR(8000) to VARCHAR(MAX), most splitter code that uses a join runs twice as slow. That's why I usually maintain two splitters... 1 for "normal" and 1 for "MAX" datatypes.[code="sql"] CREATE FUNCTION dbo.DelimitedSplit8KNEW--===== Created by Jeff Moden (Prototype: Testing Still in Progress)--===== Define I/O parameters ( @pString VARCHAR(8000), @pDelimiter CHAR(1) )RETURNS TABLE WITH SCHEMABINDING AS RETURNWITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10 E2(N) AS (SELECT 1 FROM E1 a, E1 b), --100 E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10,000cteTally(N) AS ( SELECT 0 UNION ALL SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E4 ) SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY t.N), ItemValue = SUBSTRING(@pString,t.N+1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,t.N+1),0),DATALENGTH(@pString)+1)-t.N-1) FROM cteTally t WHERE t.N BETWEEN 0 AND DATALENGTH(@pString) AND (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0);GO[/code]Mon, 14 Feb 2011 18:55:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxI looked at Jeff Moden's function and I don't know that I like being limited to 8K for that function.On the parameters, that's the way I designed it:Create Function dbo.Split(@S As VarChar(Max), @Delimiter Char(2) = '', @ByteCount Int = 0)Returns @SplitTable Table ( SplitID Int Identity(1,1), SplitText VarChar(Max) ) ASFor whatever reason, I have never run into a situation where SQL evaluating a space into a zero-length string ever mattered.I hope I don't end up having to run a replace, as Bitbucket suggested, but perhaps that's the only way out for this situation.Thanks,ChrisMon, 14 Feb 2011 13:23:53 GMTStameyhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxGiven the specification, I'd implement two parameters, one for delimiter, one for byte count. If the delimiter is null, and the byte count isn't, use the byte count, otherwise use the delimiter. That'll simplify the whole thing.Mon, 14 Feb 2011 07:27:59 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxHere is a bit a kludge, [code="sql"]Declare @Header VarChar(500)Set @Header = ' ' --this is 2 spacesIF (REPLACE( @Header,' ', '|') = '||') --replaces each space witha pipe delimeterBEGIN PRINT 'It is 2 spaces' -- just checking if it worksENDELSE PRINT 'Your guess is as good as mine' -- no it is not working[/code]Fri, 11 Feb 2011 15:08:53 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxBefore you try to write your own split function you should take a look at Jeff Moden's [url=http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589]here[/url].Fri, 11 Feb 2011 13:43:37 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxthe end goal here is a Split function, which will split a string into fields based on specifying the delimiter. This is for the ETL development I do, which takes various types of delimited data and put it into the necessary DB/table. While most data has a more common delimiter, such as a comma or pipe, sometimes is it a space, so I want my Split function to handle the space, if that's what's required. The function works fine for other delimiters, and I can also specify a byte count, such as 5, and it will split the data every 5 bytes and return it in a Table.When I pass it a space as a delimiter I get nothing back, and while troubleshooting this I found how TSQL evaluates a space, as in:If @Delimiter <> ''And unfortunately that is evaluated as False, so the function doesn't return data. That part is done to see if I have been passed a delimiter or a byte count to split on. The delimiter trumps a byte count, of both are specified.Thanks,ChrisFri, 11 Feb 2011 13:30:52 GMTStameyhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxYou can check for the length instead of the value like this.[code]Declare @Header VarChar(500)Set @Header = ' 'If DATALENGTH(@Header) > 0 print 'True'Else print 'False'[/code]Fri, 11 Feb 2011 12:53:59 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxI don't know if there's a direct way to handle that.What do you do with the parameter value in the function? There might be a workaround.Fri, 11 Feb 2011 12:12:54 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic1062759-392-1.aspxIn TSQL, I have noticed that this: [code="sql"]Declare @Header VarChar(500)Set @Header = ' 'If @Header = '' Print 'True'Else Print 'False'[/code]evaluates to True, a space character is disregarded, essentially an automatic Trim function.I wonder why this is, and if there is anything I can do to change it. I have looked at the various ANSI settings, including ANSI Padding, but I have not yet found a setting that helps this situation.It came up because I need to pass a space to a function, in some cases, and the space is "lost" in the function, which checks to see if the parameter has any length to it (>0) before further processing.Thanks,ChrisFri, 11 Feb 2011 11:32:50 GMTStamey