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]%')
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]%')
No comments:
Post a Comment