-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathSentenceFrom.sql
More file actions
132 lines (129 loc) · 5.4 KB
/
SentenceFrom.sql
File metadata and controls
132 lines (129 loc) · 5.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
CREATE OR ALTER VIEW RAN
AS
/**
Summary: >
All this does is to deceive any function into dealing with a
NewId() in a function. It causes the function to be indeterminant
and therefore will execute for each row
Author: PhilFactor
Date: 05/11/2020
Database: PhilsScripts
Examples:
- SELECT number, firstname
from ran
cross join AdventureWorks2016.person.person
Returns: >
Returns a different GUID for every line.
**/
/* */
SELECT NewId() AS number;
GO
CREATE OR ALTER FUNCTION [dbo].[SentenceFrom] (
@JsonData NVARCHAR(MAX), --the collection of objects, each one
-- consisting of arrays of strings. If a word is prepended by a
-- ^ character, it is the name of the object whose value is the array
-- of strings
@Reference NVARCHAR(100), --the JSON reference to the object containing the
-- list of strings to choose one item from.
@level INT = 5--the depth of recursion allowed . 0 means don't recurse.
)
/**
Summary: >
this function takes a json document that describes all the
alternative components
of a string and from it, it returns a string.
basically, you give it a list of alternatives and it selects one of them. However
if you put in the name of an array as one of the alternatives,rather than a word,
it will, if it selects it, treat it as a new reference and will select one of
these alternatives.
Author: PhilFactor
Date: 05/11/2020
Database: PhilsScripts
Examples:
- select dbo.SentenceFrom('{
"name":[ "^prefix ^firstname ^lastname ^suffix",
"^prefix ^firstname ^lastname","^firstname ^lastname"
],
"prefix":["Mr","Mrs","Miss","Sir","Dr","professor"
],
"firstname":["Dick","Bob","Ravi","Jess","Karen"
],
"lastname":["Stevens","Payne","Boyd","Sims","Brown"
],
"suffix":["3rd","MA","BSc","","","","",""
]
}
','$.name',5)
Returns: >
a randomised string.
**/
RETURNS NVARCHAR(MAX)
AS
BEGIN
IF coalesce(@level,-1) < 0 RETURN 'too many levels'; /* if there is mutual
references, this can potentially lead to a deadly embrace. This checks for that */
IF IsJson(@JsonData) <> 0 --check that the data is valid
BEGIN
DECLARE @Choices TABLE ([KEY] INT, value NVARCHAR(MAX));
DECLARE @words TABLE ([KEY] INT, value NVARCHAR(MAX));
DECLARE @ii INT, @iiMax INT, @Output NVARCHAR(MAX);
DECLARE @Endpunctuation VARCHAR(80); -- used to ensure we don't lose end punctuation
DECLARE @SingleWord NVARCHAR(800), @ValidJsonList NVARCHAR(800);
--we check for a missing or global reference and use the first object
IF coalesce(@Reference,'$') = '$'
SELECT top 1 @Reference = '$.'+[key] --just get the first
FROM OpenJson(@JSONData ,'$') where type=4;
insert into @choices ([key],Value) --put the choices in a temp table
SELECT [key],value FROM OpenJson(@JSONData ,@reference) where type=1
-- if there was an easy way of getting the length of the array then we
--could use JSON_VALUE ( expression , path ) to get the element
-- and get the chosen string
DECLARE @string NVARCHAR(4000) =
(SELECT TOP 1 value FROM @Choices CROSS JOIN RAN ORDER BY RAN.number);
SELECT @ValidJsonList = N'["' + Replace(string_escape(@string,'json'), ' ', '","') + N'"]';
IF IsJson(@ValidJsonList) = 0 RETURN N'invalid reference- '
+ @ValidJsonList;
--now we examine each word in the string to see if it is reference
--to another array within the JSON.
INSERT INTO @words ([KEY], value)
SELECT [KEY], value
FROM OpenJson( @ValidJsonList,'$');
IF @@RowCount = 0 RETURN @ValidJsonList + ' returned no words';
SELECT @ii = 0, @iiMax = Max([KEY]) FROM @words;
-- we now loop through the words either treating the words as strings
-- or symbols representing arrays
WHILE (@ii < (@iiMax + 1))
BEGIN
SELECT @SingleWord = value FROM @words WHERE [KEY] = @ii;
IF @@RowCount = 0
BEGIN
SELECT @Output =
N'no words in' + N'["' + Replace(@string, ' ', '","') + N'"]';
RETURN @Output;
END;
SELECT @ii = @ii + 1;
IF Left(LTrim(@SingleWord), 1) = '^'-- it is a reference
BEGIN -- nick out the '^' symbol
SELECT @Reference = '$.' + Stuff(@SingleWord, 1, 1, ''),
@Endpunctuation = '';
WHILE Reverse(@Reference) LIKE '[:;.,-_()]%'
BEGIN --rescue any punctuation after the symbol
DECLARE @End INT = Len(@Reference);
SELECT @Endpunctuation = Substring(@Reference, @End, 1);
SELECT @Reference = Substring(@Reference, 1, @End - 1);
END; --and we call it recursively
IF @level > 0
SELECT @Output =
Coalesce(@Output + ' ', '')
+ dbo.SentenceFrom(@JsonData, @Reference, @level - 1)
+ @Endpunctuation;
END;
-- otherwise it is plain sailing. Would that it were always
-- that simple
ELSE SELECT @Output = Coalesce(@Output + ' ', '') + @SingleWord;
END;
END;
ELSE SELECT @Output = 'sorry. Error in the JSON';
RETURN @Output; --and return whatever (it could be a novel!)
END;
GO