[edit: April 2019] This was originally posted to my internal (to Microsoft) blog on May 1, 2012 as part of a series I called ‘SQL Tips’ for coworkers and an email distribution list people could sign up to. I have left this as originally written but have updated formatting for my WordPress theme.
I can’t tell you how many times people have asked me to help them return a portion of a field. When the portion of the string is always the same this is pretty straight forward and simple. The “LEFT”, “RIGHT”, or “SUBSTRING” functions can do this easily. The LEFT function will return the number of characters specified from the left side of the string. The RIGHT function acts the same way but grabs the characters starting from the right side of the string. The SUBSTRING function allows you to pull a certain number of characters from the middle of the string.
Let’s take a quick look at SUBSTRING since this will be the function we use in the examples today. Books Online (BOL) shows that it takes three parameters: SUBSTRING ( value_expression , start_expression , length_expression ). Thus, you provide the field to search in, where to start returning characters, and how many characters to return. This is easy and fast if the structure for the value is essentially the same for the entire field, for example, if the column contains server names with a leading “\\” and you only want to return the server name. You could simply use something like the following to get this:
However, if you’re looking at a field that has the same structure but the number of characters is different, such as “domain\alias” or FQDN, then you’ll need something a little more dynamic. This is where CHARINDEX comes into play. CHARINDEX tells you where in a string a certain ‘expression’ is found. Let’s take for example the value “Sparklehorse\Sparkles” and show a couple examples to better understand CHARINDEX and its output.
DECLARE @ValueToSearch varchar(20) = 'SparklehorseSparkles'; SELECT @ValueToSearch AS [ValueToSearch] ,CHARINDEX('',@ValueToSearch,1) AS [Position of ""] ,CHARINDEX('Spar',@ValueToSearch,1) AS [Position of "Spar"] ,CHARINDEX('Spar',@ValueToSearch,4) AS [Position of "Spar" (searching from 4th character)];
Note that if more than one character is defined as the search criteria (such as “RE”) that the position returned is the position of the first character found when there is a match.
If we therefore use CHARINDEX to tell SUBSTRING where to begin we can pull things like “alias” out of a column that stores the domain and alias together. Let’s use an example from the ConfigMgr database, specifically the “v_GS_SYSTEM_CONSOLE_USAGE” view. If I need to get just the alias out of the “TopConsoleUser0” column then I can do that by using CHARINDEX within a SUBSTRING function, like so:
SELECT TOP 5 TopConsoleUser0 ,SUBSTRING(TopConsoleUser0,CHARINDEX(N'',TopConsoleUser0,1)+1,LEN(TopConsoleUser0)) AS [Alias] FROM dbo.v_GS_SYSTEM_CONSOLE_USAGE WITH (NOLOCK);
The CHARINDEX is finding the position of the backslash and telling SUBSTRING to start at that position (I add one though because I don’t want to include the backslash in my output). For the last parameter I’m using the LEN function, but really since I know there aren’t any characters after the alias I could put any number here as long as it is long enough to get the entire alias.
Similarly, If we wanted to get just the Domain out of the same field we could tell SUBSTRING to start at the “1” position and tell it to stop at the position where the backslash is found (subtracting one because we don’t want to return the backslash itself), such as:
SUBSTRING(TopConsoleUser0,1,CHARINDEX(N'',TopConsoleUser0,1)-1) AS [Domain]
Dynamic Start and Length numbers:
When the string you want to pull out of a field is somewhere in the middle of the field CHARINDEX comes to the rescue by using it as the starting position and as the ending position (length). For example, to pull the first table found in a “FROM” statement from the “Collection_Rules_SQL” table I would use CHARINDEX to find the “FROM” statement to use as the starting position of SUBSTRING. Then, to get the length (in this case the length of the table name directly after the “FROM”) I would find the first space after we find “FROM” (but adding 5 characters to this position to ensure we skip the space directly after “FROM”) and subtract from that position the position of where “FROM” is found. To ensure I don’t get any errors when there aren’t any characters after the first table name I use the “ABS” function (stands for absolute) to change any negative values into positive values. Here’s the sample code with some comments to help out:
SELECT TOP 10 [SQL] ,SUBSTRING([SQL] --return a portion of the SQL column ,CHARINDEX(N'FROM ',[SQL],1)+5 -- Start pulling when you see the "FROM " statement (plus 5 = number of chars in 'FROM') -- For the length, let's find the position of the first space after we find the "FROM " statement, -- then subtract the position of the "FROM " to get the length of the first table in the From statement -- The ABS makes sure we don't have a negative number (in the event there is only one table in the from statement -- and therefore, no space after the table name) ,ABS(CHARINDEX(N' ',[SQL],CHARINDEX(N'FROM ',[SQL],1)+5) -- find the position of the first space after the from statement -CHARINDEX(N'FROM',[SQL],1)-5)) -- subtract from the above the position of the from statement (make sure to include the 5 characters from "from ") AS FirstTableInFromStatement FROM dbo.Collection_Rules_SQL WITH (NOLOCK);
Output (removing the [SQL] column for space reasons; run this yourself to see it work 🙂 ):
Now you know what CHARINDEX does and how it can be used to return portions of a string in conjunction with SUBSTRING. Remember, operations like this will require a table scan so they can be pretty expensive. Make sure to only use this when you really need to.