T-SQL

TSQL : Set Line Breake on String

In TSQL, you can add line breaks to a string by using the CHAR(13) or CHAR(10) character.

For example, the following code will create a string with line breaks:

DECLARE @text AS VARCHAR(MAX) = ‘Line 1’ + CHAR(13) + ‘Line 2’ + CHAR(10) + ‘Line 3′
SELECT @text

The CHAR(13) character represents a Carriage Return (CR) and the CHAR(10) character represents a Line Feed (LF). When used together, they create a line break.

Alternatively, you can use the NCHAR function to add Unicode characters, for example:

DECLARE @text AS NVARCHAR(MAX) = N’Line 1′ + NCHAR(13) + N’Line 2′ + NCHAR(10) + N’Line 3’
SELECT @text

TSQL : Chk If Valid JSON

You can use the ISJSON function in T-SQL to check if a string is a valid JSON string. The ISJSON function returns 1 if the input string is a valid JSON string and 0 if it is not. Here is an example of how you can use the ISJSON function:

DECLARE @Json NVARCHAR(MAX) = ‘{
“id”: “2”,
“body”: “Media Msg”
}’

IF ISJSON(@Json) = 1
BEGIN
PRINT ‘Valid JSON’
END
ELSE
BEGIN
PRINT ‘Invalid JSON’
END

In this example, the ISJSON function is used to check if the string stored in the @Json variable is a valid JSON string. If the string is valid, the PRINT statement outputs “Valid JSON”. If the string is not valid, the PRINT statement outputs “Invalid JSON”.

TSQL-Split String Using CharIndex

DECLARE @btnID nVarChar(15)=’10_2′;
DECLARE @QID INT, @AID INT;

SET @QID = CAST(LEFT(@btnID, CHARINDEX(‘_’, @btnID) – 1) AS INT);
SET @AID = CAST(RIGHT(@btnID, LEN(@btnID) – CHARINDEX(‘_’, @btnID)) AS INT);

—————————————————————————–
DECLARE @strThis nVarChar(100) = ‘a,b,c,d’;
DECLARE @splitValues TABLE (Value nVarChar(100))

IF (SELECT COUNT(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STRING_SPLIT]’)) > 0
BEGIN
INSERT INTO @splitValues
SELECT value FROM STRING_SPLIT(@strThis, ‘,’)
END
ELSE
BEGIN
DECLARE @pos INT = CHARINDEX(‘,’, @strThis)
DECLARE @val nVarChar(100)

WHILE @pos > 0
BEGIN
SET @val = LEFT(@strThis, @pos – 1)
INSERT INTO @splitValues
VALUES (@val)

SET @strThis = RIGHT(@strThis, LEN(@strThis) – @pos)
SET @pos = CHARINDEX(‘,’, @strThis)
END

INSERT INTO @splitValues
VALUES (@strThis)
END

SELECT * FROM @splitValues

TSQL: Extract Next Random Question On A QUIZ

DECLARE @UserID INT = 1;
DECLARE @PrevQuestionID INT = NULL;

WITH PossibleQuestions AS
(
SELECT QuestionID, Question, Answer1, Answer2, Level
FROM Questions
WHERE PrevQuestionID = @PrevQuestionID
AND QuestionID NOT IN (SELECT QuestionID FROM UserAnswers WHERE UserID = @UserID)
),
Levels AS
(
SELECT MIN(Level) AS MinLevel
FROM PossibleQuestions
)
SELECT TOP 1 QuestionID, Question, Answer1, Answer2, Level
FROM PossibleQuestions
CROSS JOIN Levels
WHERE Level = MinLevel
ORDER BY NEWID();

Scroll to Top