T-Sql 101

I previously mentioned that there were three functions for replacing values in strings. We saw REPLACE and STUFF. The other one is relatively new (SQL Server 2017) and it’s called TRANSLATE.

Here’s an example of how I can use it to change the format of a phone number:

The first parameter is the string to work on, the second parameter is a list of individual characters to replace, and the third parameter (which must be a string the same length as the second parameter), is the set of replacement characters.

2020-01-13

For a long, long time,  users of SQL Server had requested some way to split a string. That’s a common need when working with rows from comma-delimited files (CSVs).

In the example below, I’ve asked it to break up the string ‘Greg,Tom’,Sandra’ whenever it finds a comma. Notice I could use another delimiter like TAB or semicolon instead.

The values returned are in a table. This is a table-valued function.

2020-01-06

It’s useful to be able to find one string within another string. T-SQL has two basic functions for that: CHARINDEX and PATINDEX.

CHARINDEX is used to see if one string appears within another string. Take a look at this code:

In this first example, I asked SQL Server to look for the string lot in the string Hellothere. You can see that it found it at position 4.

2019-12-30

REPLICATE is an easy function to work with. It takes a string and a number of times you want it repeated. Here’s an example:

In this case, I’ve asked it to replicate (i.e. duplicate many times) an A character. I’ve asked for 5 of them. I put an X at each end so you could see the effect more clearly.

There are many things this can be used for, but for example, if I wanted to draw a line with 100 dashes, I could just ask for REPLICATE(’-’, 100).

2019-12-23

I often need to replace characters in one string with other characters. There are three basic functions in T-SQL for doing that. The most common two functions for this are REPLACE and STUFF.

REPLACE is a simple function. You tell it which string to work on, the substring to find, and which string to replace it with. You can see it here:

I asked it to replace all occurrences of DEF with HELLO. Note that the replacement doesn’t need to be the same length as what it’s replacing. A very common use of REPLACE is to remove characters by making the replacement string just a blank string.

2019-12-16

You might need to work out how long a string is. There are two ways you could measure the length of a string. The first is the number of characters; the second is the number of bytes (i.e. the amount of data).

You can see both here:

Even though I’ve provided a Chinese string, LEN still returns 2 as the number of characters in the string.

But because they were each two-byte characters, the function DATALENGTH returns 4 as the total number of bytes.

2019-12-09

You might need to extract part of a string from a given string. T-SQL has a number of functions to help you do that. Take a look at these examples:

LEFT, RIGHT, and SUBSTRING are all functions that return parts of strings .

In the first example, we asked for 3 characters from the left of the string so SQL Server returned ABC.

The second example shows returning 2 characters from the right of the string, so we get KL.

2019-12-02

If you need to convert strings to all upper case or all lower case, you can use the UPPER and LOWER functions in T-SQL as shown below:

These are simple functions and it doesn’t matter what those strings were to start with, it will just do the case conversion.

It’s also worth noting that if you want to make your SQL query text change to upper or lower case in SQL Server Management Studio, there’s a shortcut for that too. If you want to just quickly convert a string to all uppercase or all lower case, just highlight the string and hit control shift U for upper or control shift L for lower.

2019-11-25

I often need to remove spaces from the front or back (or both) of strings. For a long time, the two functions we had for that were LTRIM (for left trim) and RTRIM (for right trim).  You can see them in the first two SELECT statements here:

For as long as I’ve used SQL Server though (also a long time), people have been asking for a TRIM function. Most developers were tired of endlessly writing LTRIM(RTRIM()) to do that. In SQL Server 2017 though, we did finally get a TRIM function. It trims both the left and right hand sides of a string.

2019-11-18

Joining together strings is called concatenating strings. In the first example here you can see SELECT hello, plus NULL, plus there. Now the trick with this, though, is that NULL is a bit of a challenge.

Note that the outcome of that first select statement is NULL is just NULL. As soon as you concatenate something with NULL, you would end up with NULL as the overall answer.

2019-11-11