String Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Left
Description: Returns a specified number of characters from the left side of a string.
Syntax: Left(String_Expression, Length)
Input: A string and a character count
Output: Substring from the left
Business Use Case: Extract year prefix from a date member name like "January 2012".
WITH MEMBER [Measures].[Year Code] AS Left([Date].[Calendar].CurrentMember.Name, 4)
SELECT
[Measures].[Year Code] ON COLUMNS, [Date].[Calendar].[Calendar Year].Members ON ROWS
FROM [Adventure Works]Right
Description: Returns a specified number of characters from the right side of a string.
Syntax: Right(String_Expression, Length)
Input: A string and a character count
Output: Substring from the right
Business Use Case: Extract the year from the end of a date string.
WITH MEMBER [Measures].[Year Suffix] AS Right([Date].[Calendar].CurrentMember.Name, 4)
SELECT
[Measures].[Year Suffix] ON COLUMNS, [Date].[Calendar].[Month].Members ON ROWS
FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]InStr
Description: Returns the position of the first occurrence of a substring within a string (1-based; 0 if not found).
Syntax: InStr([Start,] String_Expression, Search_String)
Input: Optional start position, source string, search string
Output: Integer position (1-based) or 0 if not found
Business Use Case: Check if a product name contains the word "Mountain".
WITH MEMBER [Measures].[Is Mountain Product] AS
IIF(InStr([Product].[Product Categories].CurrentMember.Name, "Mountain") > 0, "Yes", "No")
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Is Mountain Product]} ON COLUMNS,
[Product].[Product Categories].[Subcategory].Members ON ROWS FROM [Adventure Works]Len
Description: Returns the length of a string.
Syntax: Len(String_Expression)
Input: A string expression
Output: Integer character count
Business Use Case: Identify members with unusually long names for data quality checks.
WITH MEMBER [Measures].[Name Length] AS Len([Product].[Product Categories].CurrentMember.Name)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Name Length]} ON COLUMNS,
[Product].[Product Categories].[Product].Members ON ROWS FROM [Adventure Works]LTrim
Description: Removes leading whitespace characters from a string.
Syntax: LTrim(String_Expression)
Business Use Case: Clean up dimension member names that may have leading spaces from data import.
WITH MEMBER [Measures].[Clean Name] AS LTrim([Product].[Product Categories].CurrentMember.Name)
SELECT
[Measures].[Clean Name] ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]RTrim
Description: Removes trailing whitespace characters from a string.
Syntax: RTrim(String_Expression)
Business Use Case: Clean dimension member names of trailing spaces before string comparison.
WITH MEMBER [Measures].[Trimmed Name] AS RTrim([Product].[Product Categories].CurrentMember.Name)
SELECT
[Measures].[Trimmed Name] ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]CStr (String Conversion)
Description: Converts a numeric, date, or other expression to a string. (Also listed in Numeric Functions — applies to both.)
Syntax: CStr(Expression)
Business Use Case: Build a composite label combining member name with formatted sales value.
WITH MEMBER [Measures].[Category Summary] AS [Product].[Product Categories].CurrentMember.Name
+ " | Sales: "
+ CStr(Int([Measures].[Internet Sales Amount] / 1000))
+ "K" SELECT
[Measures].[Category Summary] ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]