70-461 – notes for ch. 2 – len, datalength, replace, replicate, ltrim, rtrim, format

frontis
Len doesn’t count trailing spaces, datalength does.
Replace replaces one string for another.
Replicate repeats strings.
Stuff is a positional replace
Rtrim and Ltrim remove spaces
Format is for dates and numbers

Len and DataLength

The following

select        LEN('Googletax   ')
select DATALENGTH('Googletax   ')

select        LEN('€')
select DATALENGTH('€')

select LEN('é')
select DATALENGTH('é')

…gives

9
12
1
1
1
1

If you do

select        LEN(N'Googletax   ')
select DATALENGTH(N'Googletax   ')

…the ‘N’ doubles the datalength of all the strings but not the len

9
24
1
2
1
2

Replace

…works like this

select REPLACE('Google pay the fair share', 'pay', 'shirk')


Google shirk the fair share

Replicate

…is a repetition tool

select REPLicate('Google tax fiddle' + CHAR(10), 5)

Google tax fiddle
Google tax fiddle
Google tax fiddle
Google tax fiddle
Google tax fiddle

Stuff

… is a positional replace

select stuff('Google pay their fair share', 8, 3, 'shirk')

Google shirk their fair share

Rtrim and ltrim

…do what you would expect. There is no ‘trim’ to take a little of both sides, so you have to use both together

select rtrim(ltrim('    Google tax fiddle      '))

Google tax fiddle

Formatting

Format is for commonly re-formatted things like dates and numbers

declare @var int
set @var = 97
while @var < 97 + 26
begin
  select char(@var), FORMAT(3, char(@var))
  set @var = @var +1
end

… gives

a    NULL
b    NULL
c    $3.00
d    3
e    3.000000e+000
f    3.00
g    3
h    NULL
i    NULL
j    NULL
k    NULL
l    NULL
m    NULL
n    3.00
o    NULL
p    300.00 %
q    NULL
r    NULL
s    NULL
t    NULL
u    NULL
v    NULL
w    NULL
x    3
y    NULL
z    NULL

Formats for dates is given by

declare @var int
set @var = 97
while @var < 97 + 26
begin
  select char(@var), FORMAT(GETDATE(), char(@var))
  set @var = @var +1
end

..the results being:

a    NULL
b    NULL
c    NULL
d    1/13/2015
e    NULL
f    Tuesday, January 13, 2015 3:02 PM
g    1/13/2015 3:02 PM
h    NULL
i    NULL
j    NULL
k    NULL
l    NULL
m    January 13
n    NULL
o    2015-01-13T15:02:20.9430000
p    NULL
q    NULL
r    Tue, 13 Jan 2015 15:02:20 GMT
s    2015-01-13T15:02:20
t    3:02 PM
u    2015-01-13 15:02:20Z
v    NULL
w    NULL
x    NULL
y    January, 2015
z    NULL
Advertisements