Substring isn’t it though SQL Server has a few tricks up it’s sleeve, LEFT() & RIGHT(). SELECT SUBSTRING( ‘The quick brown fox.‘, 5, 5) Pass in a string or column, tell it where to start, give it a number of characters you want to return. SQL Server’s substring function is really straight forward and works just like you’d think. If you’ve been working with databases for a while, I’m sure you’ve had to parse a string and while you’d think these are the same they work a little different and I think Oracle may surprise you a bit. In this case, the underscore ( _) which is a wildcard for any single character.Continuing the comparison between these two database giants, we dive into the substring function. Here’s another example where we introduce another wildcard character: SELECT PATINDEX('%b_er%', 'Bob likes beer.') However, here’s what happens when we don’t include the wildcard characters: SELECT PATINDEX('eer', 'Bob likes beer.')
Here’s an example: SELECT PATINDEX('%eer%', 'Bob likes beer.') CHARINDEX() on the other hand accepts a starting position, but not wildcard characters. The PATINDEX() accepts wildcard characters, but not a starting position. Where pattern is a character expression that contains the sequence to be found, and expression is the expression to be searched (typically a column). The PATINDEX() function’s syntax goes like this: PATINDEX ( '%pattern%', expression ) You basically have a choice of which one to use. The PATINDEX() function does a similar job to CHARINDEX(). This is case-insensitive because _CI stands for Case-Insensitive. Case-InsensitiveĪnd here’s a case-insensitive search: SELECT CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CI_AS) This is case-sensitive because _CS stands for Case-Sensitive. Here’s a case-sensitive search: SELECT CHARINDEX('Beer', 'Bob likes beer.' COLLATE Latin1_General_CS_AS) You can explicitly perform a case-sensitive search by adding the COLLATE clause to your SELECT statement: Case-Sensitive And we can see that its position is 17 characters from the start of the string (even though it’s only one character from where we started searching). So in this example we start searching at position 16 (which happens to be the space before the 2nd Bob). The result is that the first occurrence of Bob is skipped, and the second one’s position is returned. If this sounds confusing, the following example should help: SELECT CHARINDEX('Bob', 'Bob likes beer. However, the results are still reported based on its position within the string (not from the start position). This means that SQL Server will skip any occurrence that comes before that starting position. You can specify a starting position for where to start searching. SELECT CHARINDEX('Kate', 'Bob likes beer.
If the second argument didn’t contain Bob the result would’ve been 0.
You might also notice that Bob actually appears twice in the string, but only the position of the first match is returned. The result is 1 because that’s the position where Bob first appears in the second argument. In this example, the first argument is Bob, which means that we’re searching the second argument for Bob. Here’s an example: SELECT CHARINDEX('Bob', 'Bob likes beer. Note that only the position of the first occurrence is returned. The optional start_location can be used to specify a position within expressionToSearch for which to start searching. Where expressionToFind is the expression you want to find in the other string, and expressionToSearch is the other string. The CHARINDEX() syntax goes like this: CHARINDEX ( expressionToFind, expressionToSearch ) This function accepts 3 arguments the string to find, the string to search, and an optional start position. Here’s a quick overview of each function. In SQL Server, you can use the T-SQL CHARINDEX() function or the PATINDEX() function to find a string within another string.