CkString SQL Server Reference Documentation

CkString

Current Version: 11.6.0

The Chilkat string class.

Object Creation

DECLARE @hr int
DECLARE @ckString int
EXEC @hr = sp_OACreate 'Chilkat.CkString', @ckString OUT
IF @hr <> 0
BEGIN
    PRINT 'Failed to create ActiveX component'
    RETURN
END

-- ... use @ckString ...

EXEC @hr = sp_OADestroy @ckString

T-SQL uses the Chilkat ActiveX through the OLE Automation stored procedures. They must be enabled once on the server (EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;), and the Chilkat ActiveX registered must match the bitness of the SQL Server instance (64-bit for a 64-bit SQL Server). To bind to a specific major version of Chilkat, append the major version number to the ProgID, such as sp_OACreate 'Chilkat.CkString.11' for Chilkat v11.*.*.

sp_OACreate returns an object token (an int) that is passed as the first argument of every sp_OAMethod, sp_OAGetProperty and sp_OASetProperty call, and released with sp_OADestroy. Objects returned by methods (such as an HttpResponse or JsonObject) are also tokens received through an int OUT parameter; they must likewise be destroyed, and the OUT parameter is NULL when the method fails to return an object. Objects passed as arguments are passed by their token. When an OLE Automation procedure itself fails (non-zero @hr), sp_OAGetErrorInfo describes the error.

Data types: strings are nvarchar(4000); integers, booleans (1 or 0) and object tokens are int; dates are datetime. In the signatures on this page, @success, @iResult, @sResult and the like are the OUT variables receiving a method's return value, @iValue / @sValue receive or supply a property value, and the remaining @ variables are the method's arguments in order.

A string returned through an OUT parameter is limited to 4000 characters. For longer values, retrieve the result as a result set into a table variable instead of an OUT parameter, for example DECLARE @tmp TABLE (outputLine ntext) followed by INSERT INTO @tmp EXEC sp_OAGetProperty @ckString, 'LastErrorText'. See string length limitations for strings returned by sp_OAMethod calls.

Methods that pass or return raw byte arrays are not shown on this page, because varbinary(max) values cannot be exchanged through sp_OAMethod (see varbinary(max) limitation). Use the BinData-based alternatives (methods ending in Bd) or the base64 / hex string-encoded variants instead. Binary properties (such as LastBinaryResult) can be retrieved as a result set into a table variable, as shown in their signatures. Asynchronous (*Async) methods and event callbacks are not available from SQL Server.

Properties

NumArabic
EXEC sp_OAGetProperty @ckString, 'NumArabic', @iValue OUT
Introduced in version 9.5.0.25

The number of Arabic characters contained in this string.

top
NumAscii
EXEC sp_OAGetProperty @ckString, 'NumAscii', @iValue OUT
Introduced in version 9.5.0.25

The number of us-ascii characters contained in this string.

top
NumCentralEuro
EXEC sp_OAGetProperty @ckString, 'NumCentralEuro', @iValue OUT
Introduced in version 9.5.0.25

The number of Central European and Eastern European characters found in this string. These are characters specific to Polish, Czech, Slovak, Hungarian, Slovene, Croatian, Serbian (Latin script), Romanian and Albanian.

top
NumChars
EXEC sp_OAGetProperty @ckString, 'NumChars', @iValue OUT

To be documented soon...

top
NumChinese
EXEC sp_OAGetProperty @ckString, 'NumChinese', @iValue OUT
Introduced in version 9.5.0.25

The number of Chinese characters contained in this string.

top
NumCyrillic
EXEC sp_OAGetProperty @ckString, 'NumCyrillic', @iValue OUT
Introduced in version 9.5.0.25

The number of Cyrillic characters contained in this string. The Cyrillic alphabet also called azbuka, from the old name of the first two letters) is actually a family of alphabets, subsets of which are used by certain East and South Slavic languages ” Belarusian, Bulgarian, Macedonian, Russian, Rusyn, Serbian and Ukrainian”as well as many other languages of the former Soviet Union, Asia and Eastern Europe.

top
NumGreek
EXEC sp_OAGetProperty @ckString, 'NumGreek', @iValue OUT
Introduced in version 9.5.0.25

The number of Greek characters contained in this string.

top
NumHebrew
EXEC sp_OAGetProperty @ckString, 'NumHebrew', @iValue OUT
Introduced in version 9.5.0.25

The number of Hebrew characters contained in this string.

top
NumJapanese
EXEC sp_OAGetProperty @ckString, 'NumJapanese', @iValue OUT
Introduced in version 9.5.0.25

The number of Japanese characters contained in this string.

top
NumKorean
EXEC sp_OAGetProperty @ckString, 'NumKorean', @iValue OUT
Introduced in version 9.5.0.25

The number of Korean characters contained in this string.

top
NumLatin
EXEC sp_OAGetProperty @ckString, 'NumLatin', @iValue OUT
Introduced in version 9.5.0.25

The number of Latin characters contained in this string. Latin characters include all major Western European languages, such as German, Spanish, French, Italian, Nordic languages, etc.

Note: This is the number of chars that are Latin characters that do not fall in the us-ascii range. In other words, the 8bit Latin chars.

top
NumThai
EXEC sp_OAGetProperty @ckString, 'NumThai', @iValue OUT
Introduced in version 9.5.0.25

The number of Thai characters contained in this string.

top
SizeAnsi
EXEC sp_OAGetProperty @ckString, 'SizeAnsi', @iValue OUT

The size, in bytes, of the ANSI encoding of the string.

top
SizeUtf8
EXEC sp_OAGetProperty @ckString, 'SizeUtf8', @iValue OUT

The size, in bytes, of the utf-8 encoding of the string.

top
Str
EXEC sp_OAGetProperty @ckString, 'Str', @sValue OUT
EXEC sp_OASetProperty @ckString, 'Str', @sValue

The string value of this object.

top

Methods

AnsiCharAt
EXEC sp_OAMethod @ckString, 'AnsiCharAt', @iResult OUT, @index

Returns the ASCII value of the Nth char in the string. The 1st char is at index 0.

top
Append
EXEC sp_OAMethod @ckString, 'Append', NULL, @str

The str is appended to end of this instance.

top
AppendDateRfc3339
EXEC sp_OAMethod @ckString, 'AppendDateRfc3339', NULL, @dt

Appends a date/time string in RFC 3339 format to the end of this instance.

top
AppendDateRfc822
EXEC sp_OAMethod @ckString, 'AppendDateRfc822', NULL, @dateTime

The dateTime is appended in RFC 822 format to the end of this instance.

top
AppendEncoded
EXEC sp_OAMethod @ckString, 'AppendEncoded', NULL, @str, @encoding, @charset

Appends characters from an encoded string. The encoding may be base64, hex, url, or quoted-printable. For example, to append ABC 123 from a URL-encoded string, call:

strObj.AppendEncoded(ABC+123,url,iso-8859-1)

More Information and Examples
top
AppendToFile
EXEC sp_OAMethod @ckString, 'AppendToFile', @success OUT, @path, @charsetEncoding

Appends the contents of the string to a file. The string is first converted to the character encoding specified by charsetEncoding before being appended to the file.

More Information and Examples
top
BeginsWith
EXEC sp_OAMethod @ckString, 'BeginsWith', @success OUT, @str

Returns 1 if the string begins with str. Otherwise returns 0. This method is case sensitive.

top
ChopAfter
EXEC sp_OAMethod @ckString, 'ChopAfter', NULL, @findStr

Truncates the string after the 1st occurrence of findStr. If findStr is not present, nothing is truncated.

top
ChopBefore
EXEC sp_OAMethod @ckString, 'ChopBefore', NULL, @findStr

Truncates the string before the 1st occurrence of findStr. If findStr is not present, nothing is truncated.

top
Clear
EXEC sp_OAMethod @ckString, 'Clear', NULL

Clears the string. After calling this method, the string contains 0 characters.

top
Contains
EXEC sp_OAMethod @ckString, 'Contains', @success OUT, @substr

Returns 1 if substr is present in the string (case sensitive), otherwise returns 0.

top
EndsWith
EXEC sp_OAMethod @ckString, 'EndsWith', @success OUT, @substr

Returns 1 if the string ends with substr (case-sensitive). Otherwise returns 0.

top
GetEncoded
EXEC sp_OAMethod @ckString, 'GetEncoded', @sResult OUT, @binaryEncoding, @charsetEncoding

Returns the character string in an encoding use the charsetEncoding specified. The binaryEncoding may be base64, hex, url, or quoted-printable. For example:

(Visual Basic 6.0)

Dim s As New CkString

s.Str = èèè
Text1.Text = s.GetEncoded(hex, iso-8859-1) & vbCrLf
Text1.Text = Text.Text & s.GetEncoded(hex, utf-8) & vbCrLf
Text1.Text = Text1.Text & s.GetEncoded(hex, unicode)

'Output:
'E8E8E8
'C3A8C3A8C3A8
'E800E800E800

Returns NULL on failure

More Information and Examples
top
HtmlEntityDecode
EXEC sp_OAMethod @ckString, 'HtmlEntityDecode', NULL

Decodes any HTML entities found in the string.

More Information and Examples
top
HtmlEntityEncode
EXEC sp_OAMethod @ckString, 'HtmlEntityEncode', NULL

HTML entity encodes all special characters. (Those characters that might require HTML entity encoding.)

More Information and Examples
top
LastAnsiChar
EXEC sp_OAMethod @ckString, 'LastAnsiChar', @iResult OUT

Returns the ASCII value of the last character in the string.

top
Left
EXEC sp_OAMethod @ckString, 'Left', @sResult OUT, @numChars

Returns a string that is the leftmost numChars of this instance.

Returns NULL on failure

top
Length
EXEC sp_OAMethod @ckString, 'Length', @iResult OUT

Returns the length, in characters, of the string.

top
LoadFile
EXEC sp_OAMethod @ckString, 'LoadFile', @success OUT, @path, @charsetEncoding

Loads an entire text file into the string object. The character encoding of the text file is specified by charsetEncoding. This method allows for text files in any charset to be loaded: utf-8, Unicode, Shift_JIS, iso-8859-1, etc.

More Information and Examples
top
Matches
EXEC sp_OAMethod @ckString, 'Matches', @success OUT, @strPattern, @caseSensitive

Returns 1 if the string matches the strPattern, which may contain one or more asterisk wildcard characters. Case-sensitivity is controlled by , where 1 = case sensitive, 0 = case insensitive.

More Information and Examples
top
Mid
EXEC sp_OAMethod @ckString, 'Mid', @sResult OUT, @index, @numChars

Same as the VB/VBScript Mid function, except that the 1st char is at index 0 (i.e. it is not 1-based). Returns a substring of length numChars starting at index.

Returns NULL on failure

top
ParseDateRfc3339
EXEC sp_OAMethod @ckString, 'ParseDateRfc3339', @dtResult OUT

Converts the date string (in RFC 3339 format) to a Date type.

More Information and Examples
top
ParseDateRfc822
EXEC sp_OAMethod @ckString, 'ParseDateRfc822', @dtResult OUT

Converts the date string (in RFC 822 format) to a Date type.

More Information and Examples
top
Pluralize
EXEC sp_OAMethod @ckString, 'Pluralize', NULL

Pluralizes an English word.

More Information and Examples
top
Prepend
EXEC sp_OAMethod @ckString, 'Prepend', NULL, @str

Prepends str to this instance.

top
PunyDecode
EXEC sp_OAMethod @ckString, 'PunyDecode', NULL
Introduced in version 9.5.0.52

In-place decodes the string from punycode.

top
PunyEncode
EXEC sp_OAMethod @ckString, 'PunyEncode', NULL
Introduced in version 9.5.0.52

In-place encodes the string to punycode.

top
RemoveAnsiChar
EXEC sp_OAMethod @ckString, 'RemoveAnsiChar', NULL, @ch

Removes all occurrences of ch from the string.

top
ReplaceAll
EXEC sp_OAMethod @ckString, 'ReplaceAll', NULL, @findStr, @replaceStr

Replaces all occurrences of findStr with replaceStr. (case sensitive)

top
ReplaceAnsiChar
EXEC sp_OAMethod @ckString, 'ReplaceAnsiChar', NULL, @findCh, @replaceCh

Replaces all occurrences of findCh with replaceCh.

top
ReplaceFirst
EXEC sp_OAMethod @ckString, 'ReplaceFirst', NULL, @findStr, @replaceStr

Replaces the first occurrence of findStr with replaceStr. (case sensitive)

top
Right
EXEC sp_OAMethod @ckString, 'Right', @sResult OUT, @numChars

Returns a string that is the rightmost numChars of this instance.

Returns NULL on failure

top
SaveToFile
EXEC sp_OAMethod @ckString, 'SaveToFile', @success OUT, @path, @charsetEncoding

Saves the string to a file, using the character encoding specified by charsetEncoding. This method allows for the string to be saved using character encodings such as utf-8, Unicode, Shift-JIS, or anything else...

Returns 1 for success, 0 for failure.

More Information and Examples
top
Shorten
EXEC sp_OAMethod @ckString, 'Shorten', NULL, @numChars

Removes the final numChars from the string.

top
StrComp
EXEC sp_OAMethod @ckString, 'StrComp', @iResult OUT, @str, @caseSensitive

Lexicographically compares two strings. Returns 0 if both strings are equal. A value greater than zero indicates that the first character that does not match has a greater value in the caller's string than in str; And a value less than zero indicates the opposite.

top
StripHtml
EXEC sp_OAMethod @ckString, 'StripHtml', NULL

Strips HTML tags from a string.

More Information and Examples
top
ToCRLF
EXEC sp_OAMethod @ckString, 'ToCRLF', NULL

Converts all line endings to CRLF.

top
ToLF
EXEC sp_OAMethod @ckString, 'ToLF', NULL

Converts all line endings to bare-LF (Unix/Linux style).

top
Trim
EXEC sp_OAMethod @ckString, 'Trim', NULL

Trim SPACE and Tab characters from both ends of the string.

top
Trim2
EXEC sp_OAMethod @ckString, 'Trim2', NULL

Trim SPACE, Tab, CR, and LF characters from both ends of the string.

top
TrimInside
EXEC sp_OAMethod @ckString, 'TrimInside', NULL

Replaces all tabs, CR's, and LF's, with SPACE chars, and removes extra SPACE's so there are no occurrences of more than one SPACE char in a row.

More Information and Examples
top
Unpluralize
EXEC sp_OAMethod @ckString, 'Unpluralize', NULL

Unpluralizes an English word.

More Information and Examples
top