Many times we have to look at how many characters there are in a string. Sometimes we even need to find the position of a character or a set of characters in a particular string in a column.
In this episode, we will look at how we can accomplish this very exercise.
In SMSS, for this example we will be using the Person.EmailAddress table. We will start by writing a SQL statement to SELECT everything from this table.
We do this to get familiar with the columns and what data exist inside of them.
We will be using the EmailAddress column in our SQL statement. The first thing we want to do is count the number of characters in the EmailAddress column for each row. To accomplish this we need to modify our SQL statement.
LEN
We will first be replacing the * with EmailAddress, this is to see which email address we are working with. We calculate the length or the number of characters by using the LEN function. LEN measures the length of a column.
Lets see this in practice in SMSS.
This sort of function comes in handy, when we need to determine if there are any empty columns, or to extract a certain number of characters from a column.
CHARINDEX
Next we are going to use a another function called CharIndex. The CharIndex function allows you to find the position/index of a given character or a set of characters.
The CharIndex function takes two parameters or arguments, the first argument is the character or set of characters you hope to find; this is written inbetween quotation marks, since it will most likely be a string. The second argument is the column-name where you want to look for this value.
CharIndex(firstArg, secondArg)
Remember, to enclose the value of the first argument in single quotation marks.
To make it easier to identify we give the CharIndex query an Alias. In this example we will call it @ Position.
There you have it, two new functions you can use to find the LENgth of a set of characters in a column and CharIndex to find the position or index of a particular character or set of characters in a particular column.
😯 Now you have more tools added to your toolbelt 😯, don't forget to play around with them and think of some scenario's where they might be useful.
I hope you enjoyed this episode and get excited about learning SQL.
Top comments (0)