SQL Tip: Returning a portion of a string field – dynamically (CHARINDEX)


This was originally created in 2011/2012 in a series I dubbed "SQL Tips" for my team (via email and an internal blog site). I've decided to add these SQL Tips to my external blog. These are being posted exactly how they were written ~6 years ago...unless I found someone's name being called out...I may have edited that to protect the innocent. 🙂

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:
SELECT SUBSTRING(ServerNameFieldHere,3,30)

CHARINDEX:

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) = 'Sparklehorse\Sparkles';
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)];

Output:

ValueToSearch

Position of "\"

Position of "Spar"

Position of "Spar" (searching from 4th character)

Sparklehorse\Sparkles

13

1

14

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);

Output:

TopConsoleUser0

Alias

psl-iou-win8\local_users

local_users

Adomainname\lenapabydo

lenapabydo

ObfuscatingIsFun\gohawks

gohawks

SomethingHere\hobanana

hobanana

AnotherOne\blahblah

blahblah

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 J ):

FirstTableInFromStatement

vSMS_R_System

UnknownSystem_DISC

User_DISC

User_Group_DISC

User_DISC

User_Group_DISC

IP_Network_DISC

UnknownSystem_DISC

vSMS_R_System

vSMS_R_System

vSMS_R_System

User_DISC

vSMS_R_System

vSMS_R_System

vSMS_R_System

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.

Comments (0)

Skip to main content