SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Evaluating Spaces


Evaluating Spaces

Author
Message
Stamey
Stamey
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 1071
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.
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57965 Visits: 9730
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62799 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Stamey
Stamey
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 1071
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.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62799 Visits: 17959
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15817 Visits: 25280
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



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
GSquared
GSquared
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57965 Visits: 9730
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
Stamey
Stamey
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1345 Visits: 1071
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215747 Visits: 41981
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

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)SSC Guru (215K reputation)

Group: General Forum Members
Points: 215747 Visits: 41981
Oh yeah... I forgot to mention it... it handles a space as a delimiter, as well.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search