Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Evaluating Spaces Expand / Collapse
Author
Message
Posted Friday, February 11, 2011 11:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:40 PM
Points: 234, Visits: 930
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.
Post #1062759
Posted Friday, February 11, 2011 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1062781
Posted Friday, February 11, 2011 12:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 13,327, Visits: 12,817
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 Moden's 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)
Post #1062791
Posted Friday, February 11, 2011 1:30 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:40 PM
Points: 234, Visits: 930
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.
Post #1062810
Posted Friday, February 11, 2011 1:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:32 AM
Points: 13,327, Visits: 12,817
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 Moden's 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)
Post #1062820
Posted Friday, February 11, 2011 3:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
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
Post #1062857
Posted Monday, February 14, 2011 7:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Post #1063519
Posted Monday, February 14, 2011 1:23 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 12:40 PM
Points: 234, Visits: 930
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.
Post #1063835
Posted Monday, February 14, 2011 6:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1063954
Posted Monday, February 14, 2011 6:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:27 AM
Points: 35,770, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1063955
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse