February 11, 2011 at 11:32 am
In TSQL, I have noticed that this:
Declare @Header VarChar(500)
Set @Header = ' '
If @Header = ''
Print 'True'
Else
Print 'False'
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,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 11, 2011 at 12:12 pm
I 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 11, 2011 at 12:53 pm
You can check for the length instead of the value like this.
Declare @Header VarChar(500)
Set @Header = ' '
If DATALENGTH(@Header) > 0
print 'True'
Else
print 'False'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2011 at 1:30 pm
the 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,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 11, 2011 at 1:43 pm
Before you try to write your own split function you should take a look at Jeff Moden's here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 11, 2011 at 3:08 pm
Here is a bit a kludge,
Declare @Header VarChar(500)
Set @Header = ' ' --this is 2 spaces
IF (REPLACE( @Header,' ', '|') = '||') --replaces each space witha pipe delimeter
BEGIN
PRINT 'It is 2 spaces' -- just checking if it works
END
ELSE
PRINT 'Your guess is as good as mine' -- no it is not working
February 14, 2011 at 7:28 am
Given 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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 14, 2011 at 1:23 pm
I 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)
)
AS
For 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,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 14, 2011 at 6:55 pm
Here'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.
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
RETURN
WITH
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,000
cteTally(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
--Jeff Moden
Change is inevitable... Change for the better is not.
February 14, 2011 at 6:56 pm
Oh yeah... I forgot to mention it... it handles a space as a delimiter, as well.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2011 at 8:00 am
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
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 16, 2011 at 9:18 am
Stamey (2/16/2011)
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
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.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply