There are only four types of strings. However, there are two that exist currently in SQL Server but one day will not. The four are: char, varchar, nchar and nvarchar.

There is a previous post on this called SQL Server char and varchar Data Types. There is also another previous post that is helpful. It is called UTF-8 Character Set

ASCII and UNICODE

char and varchar work within the ASCII range. nchar and nvarchar work within the UNICODE range. We need to be aware of this when we choose which type to use. It only takes one byte to store a character in the ASCII range whereas it takes two bytes to store a character in UNICODE. Therefore if you know that all of the potential letters will be within the range of ASCII you can choose char or varchar.

Declaring Variables

You declare a variable in SQL Server T-SQL with the DECLARE word, the @ symbol as the first character of the variable name and we also need to tell the computer how much space to allow. An example: DECLARE @myCharacters as char(10). We can give it a value this way: SET @myCharacters = ‘hello’.

What is the length of the variable? Look at the code below.

DECLARE @myCharacters as char(10);
SET @myCharacters = 'hello';
SELECT @myCharacters as MyChars, len(@myCharacters) as MyLen, DATALENGTH(@myCharacters) as MyDataLength;
SET @myCharacters = 'hellothere';
SELECT @myCharacters as MyChars, len(@myCharacters) as MyLen, DATALENGTH(@myCharacters) as MyDataLength;
SET @myCharacters = 'hellothereJohn';
SELECT @myCharacters as MyChars, len(@myCharacters) as MyLen, DATALENGTH(@myCharacters) as MyDataLength;
SET @myCharacters = '';
SELECT @myCharacters as MyChars, len(@myCharacters) as MyLen, DATALENGTH(@myCharacters) as MyDataLength;
SET @myCharacters = 'hello';
SELECT @myCharacters + 'Sally', len(@myCharacters  + 'Sally') as MyLen, DATALENGTH(@myCharacters + 'Sally') as MyDataLength;
-- varchar
DECLARE @myVarChars as varchar(10) = 'hello';
SELECT @myVarChars as MyVarChars, len(@myVarChars) as MyVarLen, DATALENGTH(@myVarChars) as MyVarDataLength;
SET @myVarChars = 'hellothereJohn';
SELECT @myVarChars as MyVarChars, len(@myVarChars) as MyVarLen, DATALENGTH(@myVarChars) as MyVarDataLength;
SET @myVarChars = '';
SELECT @myVarChars as MyVarChars, len(@myVarChars) as MyVarLen, DATALENGTH(@myVarChars) as MyVarDataLength;

Here are the results from SSMS. The MyVarDataLength is not true. It takes an additional 2 bytes to store these. Therefore the very last one, for example is not zero, but 2 for MyVarDataLength.

Char Strings and Nchar Strings

If you need to use characters outside of ASCII you use nchar and nvarchar, signifying UNICODE. If you are using the Windows OS, search for the program Character Map. Locate a UNICODE character by scrolling down the list of characters until you find one that has a hex address with something other that two zeros in the first two parts (of four parts) of the address. Here is the infinity symbol: ∞. In Character Map it tells us at the bottom left part of the GUI: U+221E (0xEC): Infinity.

DECLARE @myNCharacters as nchar(10);
SET @myNCharacters = 'hello∞πӊ';
SELECT @myNCharacters as MyChars, len(@myNCharacters) as MyLen, DATALENGTH(@myNCharacters) as MyDataLength;
SET @myNCharacters = N'hello∞πӊ';
SELECT @myNCharacters as MyChars, len(@myNCharacters) as MyLen, DATALENGTH(@myNCharacters) as MyDataLength;
PRINT 'hello∞πӊ'
PRINT N'hello∞πӊ'

The range of these varchar and nvarchar is from 1 to 8000. Beyond 8000 you can use MAX. MAX is up to 2 GB. ntext, text and image data types still exist but will be removed in a future version of SQL Sever, so don’t use them.

String Functions

We can get the first two characters from a string with the function left, and the last two with right. You can use substring() to extract characters in the middle of a string. To extract the first two you use it this way: SUBSTRING(‘myText’, 1, 2) or use a variable like this: SUBSTRING(@myText, 1, 2)

You can use it in a query against a column in a table in a database. Suppose you had a table called [Authors] that had a column called [FirstNmae]. Here is how you write that query: SELECT SUBSTRING([FirstName], 1, 3) FROM [myDatabase].[dbo].[Authors]. There is also the LTRIM and RTRIM to remove spaces from each end. There is also a function called REPLACE. This one replaces all of the strings. THere is also UPPER and LOWER functions that change the case. There is also the functions FORMAT, CONCAT, QUOTENAME and others.

PRINT

PRINT is a good way to debug your scripts and stored procedures because you can output the values of string and numbers to the SSMS Results pane. You need to be aware of conversion before doing this however. The following code does not work.

DECLARE @myINT INT = 4;
PRINT @myINT
PRINT 'this is my int: ' + @myINT

Here is the output.

4
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'this is my int: ' to data type int.

Just printing the int works but when you combine it with another string you need to convert the numeric value INT to a string because SQL Server tries to convert the string to an int (implicitly) and fails. Here is how you convert the int to a string in this case, using cast.

DECLARE @myINT INT = 4;
PRINT @myINT;
PRINT 'this is my int: ' + cast(@myINT as varchar(5));