|
|
TMQL String Functions
|
Here is the list of the string manipulation functions which can be used in
where clause or within file selection list:
 Length
- contains length of the field (i.e. If Name = “Joe” than Name.Length = 3)
Try on Sample Data:
select Name.ToLower() from C:\TMSampleData\SampleData.tmc
EndsWith(<param>) - Determines whether
the end of the field matches the specified string
(<param> - string imbedded in double quotes (.i.e. If Name = “Jim Smith”
Name.EndsWith(“Smith”) will have logical true as result.
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where Name.EndsWith("Smith")
or
select * from C:\TMSampleData\SampleData.tmc where Name.EndsWith("Smith") ==
true
Will have the same result.
StartsWith(<param>) - Determines whether
the beginning of the field matches the specified string. (<param> - string
imbedded in double quotes - .i.e. If Name = “Joe Smith” than
Name.StartsWith(“Joe”) will have logical true as result.)
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where Name.StartsWith("John")
or
select * from C:\TMSampleData\SampleData.tmc where Name.StartsWith("John") ==
true
Contains(<param>) Determines whether the
beginning of the field matches the specified string.
(<param> - string imbedded in double quotes - .i.e. If Name = “Joe Smith” than
Name.Contains(“mit”) will have logical true as result.)
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where Name.Contains("1")
or
select * from C:\TMSampleData\SampleData.tmc where Name.Contains("1") == true
IndexOf (<param>) Reports the index of the first occurrence of a
string, or one or more characters, within the field. (<param> - string imbedded
in double quotes - .i.e. If Name = “Joe” than Name.IndexOf(“e”) will have 2 as
result)
Try on Sample Data:
Select Phone from C:\TMSampleData\SampleData.tmc where Phone.IndexOf("2") == 4
LastIndexOf(<param>) Reports the index position of the last
occurrence of a specified Unicode character or string within the field.
(<param> - string imbedded in double quotes - .i.e. If Name = “Jim Smith” than
Name.LastIndexOf( “i”) result is 6)
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where AddressLine.LastIndexOf("l")
== 22
PadLeft(<param>)
Right-aligns the characters in this instance, padding on the left
with spaces or a specified Unicode character for a specified total length.
(<param>- total field length (SPACE will be sued for padding) i.e. If Name =
“Joe” than Name.PadLeft(5) result will be Name = “ Joe”
Or
Total field length and character to be used for padding -i.e. If Name = “Joe”
than Name.PadLeft(5,Convert.ToChar(“.”)) result will be Name = “..Joe”)
Try on Sample Data:
select Name.PadLeft(20) from C:\TMSampleData\SampleData.tmc
or
select Name.PadLeft(20,Convert.ToChar(".")) from C:\TMSampleData\SampleData.tmc
PadRight(<param>)
Left-aligns the characters in this string, padding on the right with spaces or a
specified Unicode character, for a specified total length.
(<param>- total field length (SPACE will be sued for padding) i.e. If Name =
“Joe” than Name.PadRight(5) result will be Name = “Joe ”
Or
Total field length and character to be used for padding -i.e. If Name = “Joe”
than Name.PadRight(5,Convert.ToChar(“.”)) result will be Name = “Joe ”)
Try on Sample Data:
select Name.PadRight(20) from C:\TMSampleData\SampleData.tmc
or
select Name.PadRight(20,Convert.ToChar(".")) from
C:\TMSampleData\SampleData.tmc
Insert(<param>)
Inserts a specified instance of string at a specified index position
within the field.
(<param> - index position, string - i.e. If Name = “Joe” than Name.Insert(2,”ann”)
will have as result Name = “Joanne”)
Try on Sample Data:
select Country.Insert(1,".") from C:\TMSampleData\SampleData.tmc where
Country.EndsWith("USA")
Remove(<param>)
Deletes a specified number of characters from this instance beginning at a
specified position.
(<param> - index position, string - i.e. If Name = “Joanne” than
Name.Remove(2,3) will have as result Name = “Joe”)
Try on Sample Data:
select Name.Remove(4,1) from C:\TMSampleData\SampleData.tmc where
Name.StartsWith("John1") || Name.StartsWith("John2")
Replace(<param>)
Replaces all occurrences of a specified Unicode character or string in this
instance, with another specified Unicode character or string.
Try on Sample Data:
select Name.Replace("1"," Jr.") from C:\TMSampleData\SampleData.tmc where
Name.StartsWith("John1")
Substring(<param>) Retrieves a substring from the field.
<param> - <starting_position>, <number_of_characters>
Or
<starting_position>
(i.e. If Name = “John Smith Name.Substring(0,4) will have “John” as result and
Name.Substring(4) will have “Smith” as result)
Try on Sample Data:
select Phone.Substring(0,3) as AreaCode,Phone.Substring(3) as Exchange from
C:\TMSampleData\SampleData.tmc
ToLower()
Converts text in lowercase.
(i.e. If Name = “Joe” Name.ToLower() result is Name = “joe”)
Try on Sample Data:
select Name.ToLower() from C:\TMSampleData\SampleData.tmc
ToUpper()
Converts text in uppercase.
(i.e. If Name = “Joe” Name.ToUpper() result is Name = “JOE”)
Try on Sample Data:
select Name.ToUpper() from C:\TMSampleData\SampleData.tmc
Trim()
Removes all occurrences of a set of specified characters from the
beginning and end of the field.
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where City.Trim() == ""
TrimEnd()
Removes all occurrences of a set of characters specified in an array from the
end of the field.
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where PostalCode.Trim() == ""
TrimStart() Removes all occurrences of a set of characters specified
in an array from the beginning of the field.
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where PostalCode.TrimStart() == ""
Regex.IsMatch(<FieldName>,<RegularExpression>) Indicates whether the
regular expression (<RegularExpession>) specified finds a match in the specified
field (<FieldName>).
Try on Sample Data:
select * from C:\TMSampleData\SampleData.tmc where Regex.IsMatch(Email,
@"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")
In
this example only lines with correctly formatted e-mail (Field Name = Email)
will be displayed.
 top
|
