Publication date: 08/13/2020

String SQL Functions

Function

Native SQLite

Description

HEX( binary )

Yes

SQLite built-in function that converts a BLOB to a string of hexadecimal characters. Useful when paired with the RANDOMBLOB() function.

JLEFT( string, len, <pad> )

Like the JSL Left() function. Returns len characters from the beginning of string. If pad is specified and fewer than len characters are present in string, the result is padded with pad out to length len.

JRIGHT( string, len, <pad> )

Like the JSL Right() function. Returns len characters from the end of string. If pad is specified and fewer than len characters are present in string, the result is padded with pad at the front out to length len.

LENGTH( string )

Yes

SQLite equivalent of the ANSI standard CHAR_LENGTH() function. Returns the length of its string argument in characters.

LOCATE( string1, string2 )

POSITION( string1, string2 )

Returns the (1-based) starting position of string1 within string2, returning 0 if string1 is not found within string2.

LOWER( string )

Returns a copy of string with all uppercase characters converted to lowercase.

LTRIM( string, <trimchars> )

Yes

Trims any characters contained in trimchars from the beginning of string and returns the result. If trimchars is omitted, spaces are trimmed.

PRINTF( format, <arg1, ..., argN> )

Yes

Allows constructing strings using placeholders and arguments. See the SQLite Online documentation at https://www.sqlite.org/lang.html.

REPLACE( string, find, replace )

Yes

Replaces all instances of find in string with replace and returns the result. If replace is numeric, it is converted to a string.

REVERSE( string )

Returns a copy of string with the order of the characters reversed.

RTRIM( string, <trimchars> )

Yes

Trims any characters contained in trimchars from the end of string and returns the result. If trimchars is omitted, spaces are trimmed.

SPACE( length )

Returns a string consisting of length space characters.

SUBSTR( string, start, <length> )

Yes

Returns the substring of string starting at start (1-based) that is length characters long. If length is omitted, the substring starting at start and continuing to the end of string is returned.

TRIM( string, <trimchars> )

Trims any characters contained in trimchars from the end of string and returns the result. If trimchars is omitted, spaces are trimmed.

UPPER( string )

Returns a copy of string with all lowercase characters converted to uppercase.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).
.