Following on from my previous post regarding the SQL Server CHARINDEX function here’s some information about the SUBSTRING function for those of you, like me, tend to think in VBA!
The SQL Server equivalent of the VBA MID function is SUBSTRING.
We can use it to find a part of a string.
For example:
SUBSTRING(“This is text”,3,2) would return the string “is”.
Microsoft defines the function’s syntax as follows.
Syntax
SUBSTRING ( expression , start , length )
Arguments
expression
Is a character string, binary string, text, image, a column, or an expression that includes a column. Do not use expressions that include aggregate functions.
start
Is an integer that specifies where the substring begins.
length
Is a positive integer that specifies how many characters or bytes of the expression will be returned. If length is negative, an error is returned.
Note Because start and length specify the number of bytes when SUBSTRING is used on text data, DBCS data, such as Kanji, may result in split characters at the beginning or end of the result. This behavior is consistent with the way in which READTEXT handles DBCS. However, because of the occasional strange result, it is advisable to use ntext instead of text for DBCS characters.
0 Responses to “SQL SERVER – SUBSTRING- The Equivalent of the VBA MID Function”