70-461 – notes for ch. 2 – concat, concat_null_yields_null, substring, charindex, patindex

imagep001Anything + null = null

Concat anything with null, just returns the anything. i.e. null = ”

concat_null_yields_null makes concat work like +

substring is like the Oracle substr

charindex is like the Oracle instr

patindex is like the Oracle instr but with pattern matching

concat vs. ‘+’

A null ‘+’ anything returns a null

select 'Google are evil' + null + ' tax dodgers'
NULL

whereas concat treats null as a ‘null string’ or as ”


select concat('Google are evil', null, ' tax dodgers')
Google are evil tax dodgers

CONCAT_NULL_YIELDS_NULL

I didn’t know about the CONCAT_NULL_YIELDS_NULL setting, which does what you expect. It seems an odd thing to be able to do, but I guess NULL means different things to different people.

1> SELECT empid, country + N',' + region + N',' + city AS location
2> FROM HR.Employees
3> where empid = 9
4> go
empid location


      9 NULL

(1 rows affected)
1> SET CONCAT_NULL_YIELDS_NULL off
2> go
1> SELECT empid, country + N',' + region + N',' + city AS location
2> FROM HR.Employees
3> where empid = 9
4> go
empid location


      9 UK,,London

Reference: SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)

Substring and charindex

Substring is very like the Oracle ‘substr’ (which may actually be short for substring…I don’t remember), but the Sqlserver equivalent of the Oracle ‘instr’ is charindex, so:

1> select substring('clonehenge.com', 1, charindex('.', 'clonehenge.com') -1 )
2> go


clonehenge

Patindex is similar but you can do pattern matching:

1> select patindex('%.[xc]om%', 'clonehenge.com')
2> go


     11

To be honest, I don’t remember whether or not Oracle has an equivalent of this or not…

Anyway, the thing I typically get wrong is the preceding ‘%’. If you do this:


1> select patindex('.[xc]om%', 'clonehenge.com')
2> go


      0

(1 rows affected)

….you get no match.

Advertisements