Using a variable with the TOP clause

Suppose you want to declare a variable and use that variable with the TOP clause. The following SQL will return an error message:

DECLARE @numberRows INT = 5;

SELECT TOP @numberRows * 
FROM [mydb].[myschema].[mytable];

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘@NumberRows’.

The solution is to use brackets around the variable in the TOP clause:

DECLARE @numberRows INT = 5;

SELECT TOP (@numberRows) *
FROM [mydb].[myschema].[mytable];

How to remove all non-alphanumeric characters from a string

The following SQL illustrates a method for removing all non-alphanumeric characters from a string:

-- the string that contains unwanted spaces and an exclamation mark
DECLARE @testString VARCHAR(50) = 'Jack and Jill!';
-- keep alphanumeric characters
-- the ^ wildcard means match any single character not within
-- the set specified inside the square brackets
DECLARE @charactersToKeep VARCHAR(50) = '%[^A-Za-z0-9]%';

-- while unwanted characters are still present in @testString
-- remove the unwanted characters one by one
WHILE PatIndex(@charactersToKeep, @testString) > 0
BEGIN
	-- display the index of the unwanted character
	PRINT PatIndex(@charactersToKeep, @testString)
	
	-- use the stuff function to replace the unwanted character with an empty string
	SET @testString = Stuff(@testString, PatIndex(@charactersToKeep, @testString), 1, '')
	
	-- display the new string with the unwanted character removed
	PRINT @testString
END

How to concatenate the text in several rows into one string using the FOR XML clause

The following examples use the FOR XML clause with the PATH mode to generate XML from a SELECT query.

The final example shows how to concatenate the text in several rows into one delimited string.

First, create a table variable.

DECLARE @my_table TABLE (
    Question VARCHAR(500) NOT NULL
    ,Answer VARCHAR(500) NOT NULL);
 
INSERT INTO @my_table VALUES
('Clean, Jerk, and Snatch are terms used in which activity?','Weightlifting')
,('How many men have walked on the moon: 4, 8, or 12?','12')
,('What does a sommelier do?','Taste wine');

The table’s contents can be converted to XML with the column names as tags using

SELECT Question, Answer
FROM @my_table
FOR XML PATH;

which outputs

<row><Question>Clean, Jerk, and Snatch are terms used in which activity?</Question><Answer>Weightlifting</Answer></row><row><Question>How many men have walked on the moon: 4, 8, or 12?</Question><Answer>12</Answer></row><row><Question>What does a sommelier do?</Question><Answer>Taste wine</Answer></row>

To get rid of the row tags, we use

SELECT Question, Answer
FROM @my_table
FOR XML PATH('');

which outputs

<Question>Clean, Jerk, and Snatch are terms used in which activity?</Question><Answer>Weightlifting</Answer><Question>How many men have walked on the moon: 4, 8, or 12?</Question><Answer>12</Answer><Question>What does a sommelier do?</Question><Answer>Taste wine</Answer>

Alternatively, we can replace the row tag with a Quiz tag by using

SELECT Question, Answer
FROM @my_table
FOR XML PATH('Quiz');

which outputs

<Quiz><Question>Clean, Jerk, and Snatch are terms used in which activity?</Question><Answer>Weightlifting</Answer></Quiz><Quiz><Question>How many men have walked on the moon: 4, 8, or 12?</Question><Answer>12</Answer></Quiz><Quiz><Question>What does a sommelier do?</Question><Answer>Taste wine</Answer></Quiz>

We can just select one column, say the Answer column, using

SELECT Answer
FROM @my_table
FOR XML PATH('');

which outputs

<Answer>Weightlifting</Answer><Answer>12</Answer><Answer>Taste wine</Answer>

We can get rid of the tags and use commas instead using

SELECT Answer+','
FROM @my_table
FOR XML PATH('');

which outputs

Weightlifting,12,Taste wine,

We can get rid of the trailing comma by following the example on stackoverflow:

SELECT SUBSTRING(
            (SELECT ','+Answer
            FROM @my_table
            FOR XML PATH(''))
            ,2,1000);

which outputs

Weightlifting,12,Taste wine