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

SUBSTRING length parameter: byte vs character length... Expand / Collapse
Author
Message
Posted Monday, December 14, 2009 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:52 PM
Points: 122, Visits: 274
Hi,

I have a question regarding the documentation in BOL 2005 for the SUBSTRING function.

I've been toying around with nvarchar, and substring, and in the BOL, the SUBSTRING entry states that:


length :
Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned. length can be of type bigint


It's just not very clear to me what the expression "characters or bytes" means... I'd want to know which one it is to be honest...

For example:

declare @a nvarchar(10)
set @a = N'üThis is a test'
if datalength(@a) >= 20 begin
set @a = substring(@a, 1, 7) + N'...'
end

select @a

I would have thought that my substring function would need 14 bytes as a length parameter, not 7 characters in order to truncate my expression. Could this be related to my regional settings?

Thanks,

Greg
Post #834024
Posted Monday, December 14, 2009 11:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 12,905, Visits: 32,166
yeah it seems misleading.
I would say it is ALWAYS Characters. it's just coincidence that when you have a varchar, the bytes=# chars. i think that was what they were trying to say in BOL.

when it's nvarchar, the bytes are doubles, but it's still characters the function expects.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #834037
Posted Monday, December 14, 2009 12:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:52 PM
Points: 122, Visits: 274
That's what I figured as well but I just wanted to make sure that's what it meant, and not that it depended on some regional/collation setting somewhere that it would either use byte or char as a unit.

Thanks!

Greg
Post #834054
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse