I need to make a function for coding string in which i make difference
between strings that are identical except from final blank spaces. In SQL 200
exists an option (collate set or something else) that can make SQL
distinguish betwene two string like these:
'MYSTRING1'
'MTSTRING1    ' 

Thanks

One method...

if(cast('as  ' as varbinary) = cast('as' as varbinary))
print 'match'
else
print 'does not match'

Try this:
USE tempdb
GO
CREATE FUNCTION MyStringCompare(@str1 nvarchar(MAX), @str2 nvarchar(MAX))
    RETURNS nvarchar(15)
AS
BEGIN
    IF ((@str1 + N'#') = (@str2 + N'#'))
        RETURN N'match'
    RETURN N'don''t match'
END
GO

SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1')  -- -> match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1  ') -- -> don't
match
SELECT tempdb.dbo.MyStringCompare(null, N'MYSTRING1  ') -- -> don't match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', null) -- -> don't match
SELECT tempdb.dbo.MyStringCompare(null, null) -- -> don't match

Greetings,
Urs

Show quoteHide quote

How about this:

declare @a varchar(30)
declare @b varchar(30)

set @a = 'MYSTRING1'
set @b = 'MTSTRING1    '

IF (@a = @b AND DATALENGTH(@a) = DATALENGTH(@b))
PRINT 'Matched'
ELSE
PRINT 'Different'

Roy Harvey
Beacon Falls, CT


On Fri, 30 Jun 2006 00:26:01 -0700, checcouno
<checco***@discussions.microsoft.com> wrote:

Show quoteHide quote

Be very careful about this.  SQL pads shorter strings with blanks for
comparing them, so your function could destroy expected behavior and
you would wind up with your own private language.  Otherwise, use the
DATALENGTH() function; it is standard and portable.

SQLXML 4.0 "timestamp" updategram question
Identity keys in a One-to-Zero-or-Many relationship
Insert in every ID
Exception_Access_Violation Error?
Contents of a SP