- Date and Time Functions
- Mathematical Functions
- Text and Image Functions
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Text and Image Functions
Text and image functions operate on the TEXT, NTEXT and IMAGE data types. Here's a database design hint: In most cases, it is prudent to keep large text in a document on the file system and store a link to that document within the database. Columns with TEXT data type do not support commonly used string functions such as LEN, LEFT, RIGHT, and so on. Further, due to the large size of TEXT data, you're likely to have performance issues if you store such data in the database. However, you can't help but use the TEXT data type sometimes—this is when text functions come in handy.
The PATINDEX() function is useful with the TEXT data type, but it can also be used with columns having the CHAR or VARCHAR data types. In fact, PATINDEX() is very similar to CHARINDEX(), which tends to be used more frequently with string expressions. PATINDEX() seeks for the first occurrence of a pattern within a string. If the pattern is found, PATINDEX() returns the character number where the first occurrence of the pattern begins. For instance, the following query creates a table variable with the TEXT data type and then uses PATINDEX to search for occurrences of the "tex" pattern:
DECLARE @table TABLE (
a TEXT)
INSERT @table
VALUES ('ouch, this is some really long text')
SELECT PATINDEX('%tex%', a) FROM @table
Results:
----------- 32
Notice that the expression sought by PATINDEX() must be enclosed in "%" signs. Within the expression we could use wildcards "%" for any number of letters, "_" for a single character, [] to specify a range of characters and [^] to skip a range, just as you do when using LIKE comparisons. For instance, you can modify the previous query slightly with a wildcard and get a different result:
SELECT PATINDEX('%t%x%', a) FROM @table
Results:
----------- 7
CHARINDEX() differs from PATINDEX() in that it searches for an exact match, in other words, occurrence of one expression within another, rather than searching for a pattern. Therefore, you cannot use wildcards with CHARINDEX; if you do, the wildcards will be interpreted as if you were searching for that letter ("%" or "_").
The TEXTPTR() function returns the binary pointer to the TEXT, NTEXT, or IMAGE column in a table. After you get the binary value from TEXTPTR(), you can use it for READTEXT, WRITETEXT, and UPDATETEXT statements. For instance, the following query uses TEXTPTR to get pointers to the text column values within the table variable:
DECLARE @table TABLE (
a TEXT)
INSERT @table
VALUES ('ouch, this is some really long text')
INSERT @table
VALUES ('here is some more text')
SELECT TEXTPTR(a) FROM @table
Results:
---------------------------------- 0x0100010000000000EC6FB91F00000000 0x0100020000000000EC6FB91F01000000
The TEXTVALID() function provides a way to see whether an existing pointer to a column with TEXT, NTEXT, or IMAGE data type is valid. The next batch of SQL statements creates a temporary table with a TEXT data type, adds a row, gets the pointer, and then ensures that the pointer is valid:
CREATE TABLE #table (
a TEXT)
DECLARE @textptr VARBINARY(320)
INSERT #table
VALUES ('ouch, this is some really long text')
SELECT @textptr = TEXTPTR(a) FROM #table
SELECT TEXTVALID('#table.a', @textptr) AS is_pointer_valid
DROP TABLE #table
Results:
is_pointer_valid ---------------- 1