SQL String Manipulations

Interesting article in SQL Server Magazine about String Manipulations by Itzik Ben-Gan (http://www.sqlmag.com/article/tsql/string-manipulation-tips-techniques-part-1-136427)

So to give me a quick reference in the future, I'll summarize it here:

Counting Occurrences of a subString within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello';

SELECT (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr);


Exactly N Occurrences of a substring within a string:

DECLARE
  @str    AS VARCHAR(1000) = 'abchellodehellofhello',
  @substr AS VARCHAR(1000) = 'hello',
  @N      AS INT           = 3;

SELECT
  CASE
    WHEN (LEN(@str) - LEN(REPLACE(@str, @substr, ''))) / LEN(@substr) = @N
      THEN 'True'
    ELSE 'False or Unknown'
  END;
-OR-

SELECT
  CASE
    WHEN @str LIKE '%' + REPLICATE(@substr + '%', @N)
         AND @str NOT LIKE '%' + REPLICATE(@substr + '%', @N+1)
      THEN 'True'
    ELSE 'False or Unknown'
  END;

Replacing Multiple Contiguous Spaces with a single space:

DECLARE @str AS VARCHAR(1000) = 'this   is     a   string    with     lots   of   spaces';

SELECT REPLACE(REPLACE(REPLACE(@str, ' ', '~ '), ' ~', ''), '~ ', ' ');

Replacing Overlapping Occurrences:

DECLARE @str AS VARCHAR(1000) = '.x.x.x.x.';

SELECT REPLACE(REPLACE(@str, '.x.', '.y.'), '.x.', '.y.');

-OR-

SELECT REPLACE(REPLACE(REPLACE(@str, '.', '..'), '.x.', '.y.'), '..', '.');
String Formatting Numbers with Leading Zeros:

DECLARE @num AS INT = -1759;

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + REPLACE(STR(ABS(@num), 10), ' ', '0');

-OR-

SELECT CASE SIGN(@num) WHEN -1 THEN '-' ELSE '' END + RIGHT('000000000' + CAST(ABS(@num) AS VARCHAR(10)), 10);

-OR (In Denali)-

SELECT FORMAT(@num, '0000000000');

Left Trimming Leading Occurrences of a Character:

DECLARE @str AS VARCHAR(100) = '0000001709';

SELECT REPLACE(LTRIM(REPLACE(@str, '0', ' ')), ' ', '0');

Checking That a String Is Made of Only Digits:

DECLARE @str AS VARCHAR(1000) = '1759';
SELECT
  CASE
    WHEN @str NOT LIKE '%[^0-9]%' THEN 'True'
    ELSE 'False or Unknown'
  END;

-OR-

CHECK (col1 NOT LIKE '%[^0-9]%')