Select statement with NULL and Find No. of the character from string

Some of the rows in the Customers table have a NULL in the region column. For those, SQL Server returns by default a NULL in the location result column:

custid      country         region city            location
----------- --------------- ------ --------------- -------------------
1           Germany         NULL   Berlin          NULL
2           Mexico          NULL   México D.F.     NULL
3           Mexico          NULL   México D.F.     NULL
4           UK              NULL   London          NULL
5           Sweden          NULL   Luleå           NULL
6           Germany         NULL   Mannheim        NULL
7           France          NULL   Strasbourg      NULL
8           Spain           NULL   Madrid          NULL
9           France          NULL   Marseille       NULL
10          Canada          BC     Tsawassen       Canada,BC,Tsawassen
11          UK              NULL   London          NULL
12          Argentina       NULL   Buenos Aires    NULL
13          Mexico          NULL   México D.F.     NULL
14          Switzerland     NULL   Bern            NULL
15          Brazil          SP     Sao Paulo       Brazil,SP,Sao Paulo
16          UK              NULL   London          NULL
17          Germany         NULL   Aachen          NULL
18          France          NULL   Nantes          NULL
19          UK              NULL   London          NULL
20          Austria         NULL   Graz            NULL
...

(91 row(s) affected)

You can change the way SQL Server treats concatenation by setting a session option called CONCAT_NULL_YIELDS_NULL to OFF. SQL Server treats a NULL set to OFF as an empty string for concatenation purposes. To demonstrate this behavior, run the following code to set the option to OFF, and then rerun the query in above result

SET CONCAT_NULL_YIELDS_NULL OFF;

----------------------------------------------------------------------------------

Find No. of the character  from string

SELECT empid, lastname,
  LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur
FROM HR.Employees;