Sql server® 2012 t-sql fundamentals

The 

PATINDEX

 Function

The PATINDEX function returns the position of the first occurrence of a pattern within a string.

Syntax

PATINDEX(pattern, string)

The argument pattern uses similar patterns to those used by the LIKE predicate in T-SQL. I’ll explain 

patterns and the LIKE predicate later in this chapter, in “The LIKE Predicate.” Even though I haven’t 

explained yet how patterns are expressed in T-SQL, I include the following example here to show how 

to find the position of the first occurrence of a digit within a string.

SELECT PATINDEX('%[0-9]%', 'abcd123efgh');

This code returns the output 5.

The 

REPLACE

 Function

The REPLACE function replaces all occurrences of a substring with another.

Syntax

REPLACE(string, substring1, substring2)

The function replaces all occurrences of substring1 in string with substring2. For example, the fol-

lowing code substitutes all occurrences of a dash in the input string with a colon.

SELECT REPLACE('1-a 2-b', '-', ':');

This code returns the output: ‘1:a 2:b’.

You can use the REPLACE function to count the number of occurrences of a character within a 

string. To do this, you replace all occurrences of the character with an empty string (zero characters) 

and calculate the original length of the string minus the new length. For example, the following query 

returns, for each employee, the number of times the character e appears in the lastname attribute.

SELECT empid, lastname, 

  LEN(lastname) - LEN(REPLACE(lastname, 'e', '')) AS numoccur 

FROM HR.Employees;

This query generates the following output.

empid       lastname             numoccur 

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

5           Buck                 0 

8           Cameron              1 

1           Davis                0 

9           Dolgopyatova         0 

2           Funk                 0 

7           King                 0 

3           Lew                  1 

4           Peled                2 

6           Suurs                0

www.it-ebooks.info

---