Skip to content

Commit 3ed2b4c

Browse files
ktaranovktaranov
committed
Add dbo.udf_InitCap function
Fix data types upper case in udf_LCS function
1 parent f48a932 commit 3ed2b4c

2 files changed

Lines changed: 85 additions & 21 deletions

File tree

‎User_Defined_Function/dbo.udf_InitCap.sql‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,64 @@
1+
IF OBJECT_ID('dbo.udf_InitCap') IS NULL
2+
EXECUTE ('CREATE FUNCTION dbo.udf_InitCap() RETURNS nvarchar(max) AS RETURN SELECT 1 AS A;');
3+
GO
4+
5+
ALTER FUNCTION dbo.udf_InitCap(
6+
@StrStr nvarchar(max)
7+
)
8+
/*
9+
SELECT dbo.udf_InitCap(N'test STRING ß');
10+
11+
Source link: https://github.com/ktaranov/sqlserver-kit/blob/master/User_Defined_Function/dbo.udf_InitCap.sql
12+
Original link: https://gist.github.com/jef-sure/f67bc2c06c4804ca832f1dc5550c8db4
13+
Author: Anton Petrusevich
14+
Created Date: 2018-09-24
15+
Modified Date: 2019-07-23 by Konstantin Taranov
16+
*/
17+
RETURNS nvarchar(max) AS
18+
BEGIN
19+
DECLARE @StrNew nvarchar(max);
20+
DECLARE @c nvarchar(1);
21+
DECLARE @x integer;
22+
DECLARE @StrLen integer;
23+
DECLARE @CloseBracket nvarchar(5);
24+
DECLARE @OpenBracket varchar(5);
25+
DECLARE @mode varchar(20) = 'upper';
26+
SELECT @StrNew = '', @x = 1, @StrLen = LEN(@StrStr)+1;
27+
SELECT @OpenBracket = CHAR(34) + CHAR(39) + CHAR(40) + CHAR(91) + CHAR(123);
28+
SELECT @CloseBracket = CHAR(34) + CHAR(39) + CHAR(41) + CHAR(93) + CHAR(125);
29+
WHILE @x < @StrLen
30+
BEGIN
31+
SET @c = SUBSTRING(@StrStr, @x, 1);
32+
DECLARE @pbi INT;
33+
DECLARE @cb NCHAR(1);
34+
IF @c = 'ß' or unicode(LOWER(@c)) <> unicode(UPPER(@c))
35+
BEGIN
36+
IF @mode = 'lower'
37+
SET @c = lower(@c);
38+
IF @mode = 'upper'
39+
BEGIN
40+
SET @c = upper(@c);
41+
SET @mode = 'lower';
42+
END;
43+
END;
44+
ELSE
45+
BEGIN
46+
SET @pbi = CHARINDEX(@c, @OpenBracket);
47+
IF @pbi <> 0
48+
BEGIN
49+
SET @cb = SUBSTRING(@CloseBracket, @pbi, 1);
50+
SET @mode = 'preserve';
51+
END
52+
ELSE
53+
IF @mode <> 'preserve'
54+
SET @mode = 'upper';
55+
ELSE
56+
IF @c = @cb
57+
SET @mode = 'upper';
58+
END;
59+
SET @StrNew = @StrNew + @c;
60+
SET @x = @x +1;
61+
END;
62+
RETURN @StrNew;
63+
END;
64+
GO

‎User_Defined_Function/dbo.udf_LCS.sql‎

Lines changed: 21 additions & 21 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
IF OBJECT_ID('dbo.udf_LCS') IS NULL
2-
EXECUTE ('CREATE FUNCTION dbo.udf_LCS() RETURNS NVARCHAR(MAX) AS RETURN SELECT 1 AS A;');
2+
EXECUTE ('CREATE FUNCTION dbo.udf_LCS() RETURNS nvarchar(max) AS RETURN SELECT 1 AS A;');
33
GO
44

55

@@ -25,22 +25,22 @@ example:
2525
returns: >
2626
the longest common subsequence as a string
2727
**/
28-
(@xString VARCHAR(MAX), @yString VARCHAR(MAX))
29-
RETURNS VARCHAR(MAX)
28+
(@xString varchar(max), @yString varchar(max))
29+
RETURNS varchar(max)
3030
AS
3131
BEGIN
3232

33-
DECLARE @ii INT = 1; --inner index
34-
DECLARE @jj INT = 1; --next loop index
35-
DECLARE @West INT; --array reference number to left
36-
DECLARE @NorthWest INT; --array reference previous left
37-
DECLARE @North INT; --array reference previous
38-
DECLARE @Max INT; --holds the maximum of two values
39-
DECLARE @Current INT; --current number of matches
40-
DECLARE @Matrix NVARCHAR(MAX);
41-
DECLARE @PreviousRow NVARCHAR(2000); -- the previous matrix row
42-
DECLARE @JSON NVARCHAR(4000); --json work variable
43-
DECLARE @Numbers TABLE (jj INT);
33+
DECLARE @ii int = 1; --inner index
34+
DECLARE @jj int = 1; --next loop index
35+
DECLARE @West int; --array reference number to left
36+
DECLARE @NorthWest int; --array reference previous left
37+
DECLARE @North int; --array reference previous
38+
DECLARE @Max int; --holds the maximum of two values
39+
DECLARE @Current int; --current number of matches
40+
DECLARE @Matrix nvarchar(max);
41+
DECLARE @PreviousRow nvarchar(2000); -- the previous matrix row
42+
DECLARE @JSON nvarchar(4000); --json work variable
43+
DECLARE @Numbers TABLE (jj int);
4444
-- SQL Prompt formatting off
4545

4646
INSERT INTO @Numbers(jj) --this is designed for words of max 40 characters
@@ -59,16 +59,16 @@ VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
5959
SELECT @West = 0, @JSON = NULL;
6060
--now create a row in just one query
6161
SELECT @NorthWest =
62-
Json_Value(@PreviousRow, '$[' + Cast(jj - 1 AS VARCHAR(5)) + ']'),
62+
Json_Value(@PreviousRow, '$[' + Cast(jj - 1 AS varchar(5)) + ']'),
6363
@North =
64-
Json_Value(@PreviousRow, '$[' + Cast(jj AS VARCHAR(5)) + ']'),
64+
Json_Value(@PreviousRow, '$[' + Cast(jj AS varchar(5)) + ']'),
6565
@Max = CASE WHEN @West > @North THEN @West ELSE @North END,
6666
@Current =
6767
CASE WHEN Substring(@xString, jj, 1) = Substring(@yString, @ii, 1) THEN
6868
@NorthWest + 1 ELSE @Max END,
6969
@JSON =
7070
Coalesce(@JSON + ',', '[0,')
71-
+ Coalesce(Cast(@Current AS VARCHAR(5)), 'null'), @West = @Current
71+
+ Coalesce(Cast(@Current AS varchar(5)), 'null'), @West = @Current
7272
FROM @Numbers AS f
7373
WHERE f.jj <= Len(@xString);
7474
--and store the result as the previous row
@@ -97,14 +97,14 @@ VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),
9797
SELECT @PreviousRowScore =
9898
Json_Value(
9999
@Matrix,
100-
'strict $[' + Convert(VARCHAR(5), @ii - 1) + ']['
101-
+ Convert(VARCHAR(5), @jj) + ']'
100+
'strict $[' + Convert(varchar(5), @ii - 1) + ']['
101+
+ Convert(varchar(5), @jj) + ']'
102102
),
103103
@previousColScore =
104104
Json_Value(
105105
@Matrix,
106-
'strict $[' + Convert(VARCHAR(5), @ii) + ']['
107-
+ Convert(VARCHAR(5), @jj - 1) + ']'
106+
'strict $[' + Convert(varchar(5), @ii) + ']['
107+
+ Convert(varchar(5), @jj - 1) + ']'
108108
);
109109
--either go north or west
110110
IF @PreviousRowScore < @previousColScore SELECT @jj = @jj - 1;

0 commit comments

Comments
 (0)