DECLARE @Space nvarchar(10)
SET @Space = ' '
PRINT 'Len() of a space: '''+CAST(Len(@Space) AS varchar(20))+''' (ouch)'
IF @Space = ' '
	PRINT 'Space = space: True (as expected)'
ELSE
	PRINT 'Space = space: False'
GO
DECLARE @Space nvarchar(10)
SET @Space = ''
PRINT 'Len() of a blank: '''+CAST(Len(@Space) AS varchar(20))+''' (as expected)'
IF @Space = ' '
	PRINT 'Blank = space: True (ouch)'
ELSE
	PRINT 'Blank = space: False'
GO

declare @Space nvarchar(10)
declare @Space2 nvarchar(10)

set @Space = ''
set @Space2 = ' '

select [len] = len(@Space2),[datalength]=datalength(@Space2)

if @Space = @Space2
	print '@Space = @Space2'
else
	print '@Space <> @Space2'

if  convert(varbinary(20),@Space) = convert(varbinary(20),@Space2)
	print 'Binary @Space = Binary @Space2'
else
	print 'Binary @Space <> Binary @Space2'


if @Space like @Space2
	print '@Space Like @Space2'
else
	print '@Space Not Like @Space2'



len         datalength  
----------- ----------- 
0           2

(1 row(s) affected)

@Space = @Space2
Binary @Space <> Binary @Space2
@Space Not Like @Space2

quote:

---

but with LIKE all characters are compared

---

declare @Space nvarchar(10)
declare @Space2 nvarchar(10)

set @Space = ''
set @Space2 = ' '

if @Space like @Space2
	print '@Space Like @Space2'
else
	print '@Space Not Like @Space2'

if @Space2 like @Space
	print '@Space2 Like @Space'
else
	print '@Space2 Not Like @Space'

@Space Not Like @Space2
@Space2 Like @Space

quote:

---

Originally posted by Michael Valentine Jones

When SQL Server compares two strings, it makes a comparison from left to right and treats the shorter expression as though it was padded with spaces to equal the length of the longer string.

---

quote:

---

Originally posted by Kristen

RTRIM = LEFT(MyColumn, LEN(MyColumn))

---

quote:

---

Originally posted by Arnold Fribble
It's not symmetrical: as BOM explains, LIKE treats trailing spaces in the pattern (RHS) as significant, but not in the match_expression (LHS).

---

quote:

---

Originally posted by Marioi

quote:

---

Originally posted by Kristen

RTRIM = LEFT(MyColumn, LEN(MyColumn))

---

These behaviors (space equals blank, which is not a null, which doesn't equal null) make me wonder how people build robust queries, let alone more generic solutions.

---

quote:

---

Originally posted by jsmith8858
The main thing is: why is a space important in your data?

---

quote:

---

Originally posted by Arnold Fribble

Oops, should have tested that, shouldn't I?

---

Print 'len of @Space2 as varbinary: '''+CAST(Len(CAST(@Space2 AS varbinary(20))) AS varchar(20))+''''

len of @Space2 as varbinary: '2'

declare @Space nchar(10)
declare @Space2 nchar(10)

set @Space = ''
set @Space2 = ' '

Print 'EXAMPLE WITH nchar:'
Print 'len of @Space2: '''+CAST(len(@Space2) AS varchar(20))+''''
Print 'datalength of @space2: '''+CAST(datalength(@Space2) AS varchar(20))+''''
Print '-------------------'

Print 'len of @Space2 as varbinary: '''+CAST(Len(CAST(@Space2 AS varbinary(20))) AS varchar(20))+''''
Print 'datalength of @Space2 as varbinary: '''+CAST(datalength(CAST(@Space2 AS varbinary(20))) AS varchar(20))+''''
Print '-------------------'

if @Space = @Space2
	print '@Space = @Space2'
else
	print '@Space <> @Space2'

if  CAST(@Space AS varbinary(20)) = CAST(@Space2 AS varbinary(20))
	print 'Binary @Space = Binary @Space2'
else
	print 'Binary @Space <> Binary @Space2'


if @Space like @Space2
	print '@Space Like @Space2'
else
	print '@Space Not Like @Space2'

PRINT '-------------------'

-- Testing whether LIKE is symmetrical.

if @Space like @Space2
	print '@Space Like @Space2'
else
	print '@Space Not Like @Space2'

if @Space2 like @Space
	print '@Space2 Like @Space'
else
	print '@Space2 Not Like @Space'
GO

EXAMPLE WITH nchar:
len of @Space2: '0'
datalength of @space2: '20'
-------------------
len of @Space2 as varbinary: '20'
datalength of @Space2 as varbinary: '20'
-------------------
@Space = @Space2
Binary @Space = Binary @Space2
@Space Like @Space2
-------------------
@Space Like @Space2
@Space2 Like @Space
 
EXAMPLE WITH nvarchar:
len of @Space2: '0'
datalength of @space2: '2'
-------------------
len of @Space2 as varbinary: '2'
datalength of @Space2 as varbinary: '2'
-------------------
@Space = @Space2
Binary @Space <> Binary @Space2
@Space Not Like @Space2
-------------------
@Space Not Like @Space2
@Space2 Not Like @Space

quote:

---

Originally posted by Marioi
But with nvarchar, why is len of varbinary returning 2

---

quote:

---

Originally posted by Kristen
Where trailing spaces are significant??

---

quote:

---

Originally posted by jsmith8858

A specific example would be helpful.

---

IF EXISTS (SELECT * 
	   FROM   sysobjects 
	   WHERE  name = N'fnSplit')
	DROP FUNCTION fnSplit
GO

------------------ fnSplit ----------------------

CREATE FUNCTION dbo.fnSplit
	(@RowData nvarchar(4000),
	@SplitOn nvarchar(5))

-- AUTHOR: MI 11/30/05
--	Adapted from Seventhnight's posting on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648.
--	The function preserved starting and trailing spaces.
--	IMPORTANT: Current does NOT support spaces as delimiters. 
-- PURPOSE: 
--	Split a uniformly delimited string into a table.
-- PARAMETERS:
--	@RowData: the string to split.
--	@SplitOn: the delimiter.
-- RETURNS:
--	Table with 2 columns: counter and Data.
-- EXAMPLE 1:
-- 	SELECT * FROM dbo.fnSplit 
-- 		('Test|string||that needs to|be||split', 
-- 		 '|')
-- 	GO
-- EXAMPLE 2:
-- 	SELECT * FROM dbo.fnSplit 
-- 		('  Test\\\   string\\\that needs to  \\\\\\ be \\\   split    \\\\\\', 
-- 		 '\\\')
-- 	GO

RETURNS @RtnValue table 
	(Id int identity(1,1),
	Data nvarchar(4000)) 
AS  
BEGIN 
	-- Ensure that the SplitOn parameter has at least 1 character. If not,
	-- we would loop infinitely
	-- Len returns 0 when only spaces are in @SplitOn. Spaces are not
	-- not supported in @SplitOn because they cause an infinite loop
	-- further down.
	IF Len(@SplitOn) < 1
	BEGIN
-- 		RAISERROR ('Please provide a valid character in the %d parameter.',
-- 		      16, 1, '@SplitOn')
		Return
	END
	ELSE
	BEGIN
		Declare @Cnt int
		Declare @sValue nvarchar(4000)
		Set @Cnt = 1
	
		While (Charindex(@SplitOn,@RowData)>0)
		Begin
			(Select @sValue = Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))	-- removed LTrim(RTrim())
			
			Insert Into @RtnValue (data)
				Select Data = @sValue
	
			Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+Len(@SplitOn),len(@RowData))
			Set @Cnt = @Cnt + 1
		End
	
		Select @sValue = @RowData	-- removed LTrim(RTrim())
		Insert Into @RtnValue (data)
		Select Data = @sValue
	END
	Return
END

GO


-- =============================================
-- Example to execute function
-- =============================================
SELECT * FROM dbo.fnSplit 
	('  Test\\\   string\\\that needs to  \\\\\\ be \\\   split    \\\\\\', 
	 '\\\')
GO

"This  is  a string"

1) "This ", "is ", "a", "string"

or

2) "This", "", "is", "", "a","string"

or

3) "This", " is", " a","string"

or

4) "This", " is ", "a", "string"

etc

"hello   there"

1) "hello"," ","there" 
2) "hello   ","there"
3) "hello "," there"
4) "hello","  there" 
5) "hello"," "," "," ","there" 
6) "hello","","","","there"
etc

quote:

---

Originally posted by jsmith8858
What about

"hello   there"

(3 spaces) ?

---

quote:

---

LEN Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH Returns the number of bytes used to represent any expression.

---

quote:

---

CREATE FUNCTION dbo.fnSplit
(@RowData nvarchar(4000),
@SplitOn nvarchar(5))

-- AUTHOR: MI 11/30/05
-- Adapted from Seventhnight's posting on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648.
-- The function preserved starting and trailing spaces.
-- IMPORTANT: Current does NOT support spaces as delimiters.
-- PURPOSE:
-- Split a uniformly delimited string into a table.
-- PARAMETERS:
-- @RowData: the string to split.
-- @SplitOn: the delimiter.
-- RETURNS:
-- Table with 2 columns: counter and Data.
-- EXAMPLE 1:
-- SELECT * FROM dbo.fnSplit
-- ('Test|string||that needs to|be||split',
-- '|')
-- GO
-- EXAMPLE 2:
-- SELECT * FROM dbo.fnSplit
-- (' Test\\\ string\\\that needs to \\\\\\ be \\\ split \\\\\\',
-- '\\\')
-- GO

RETURNS @RtnValue table
(Id int identity(1,1),
Data nvarchar(4000))
AS
BEGIN
-- Ensure that the SplitOn parameter has at least 1 character. If not,
-- we would loop infinitely
-- Len returns 0 when only spaces are in @SplitOn. Spaces are not
-- not supported in @SplitOn because they cause an infinite loop
-- further down.
IF Len(@SplitOn) < 1
BEGIN
-- RAISERROR ('Please provide a valid character in the %d parameter.',
-- 16, 1, '@SplitOn')
Return
END
ELSE
BEGIN
Declare @Cnt int
Declare @sValue nvarchar(4000)
Set @Cnt = 1

While (Charindex(@SplitOn,@RowData)>0)
Begin
(Select @sValue = Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)) -- removed LTrim(RTrim())

Insert Into @RtnValue (data)
Select Data = @sValue

Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+Len(@SplitOn),len(@RowData))
Set @Cnt = @Cnt + 1
End

Select @sValue = @RowData -- removed LTrim(RTrim())
Insert Into @RtnValue (data)
Select Data = @sValue
END
Return
END

---