String – upper/lower case [examples]

Convert character expression to upper case

? upper("Lianja")
LIANJA

https://www.lianja.com/doc/index.php/UPPER()


Convert character expression to upper case

? ucase("Lianja")
LIANJA

https://www.lianja.com/doc/index.php/UCASE()


Test for lower case character

use prospect
replace all company with proper(company);
  for islower(company)

https://www.lianja.com/doc/index.php/ISLOWER()


Test for upper case character

use prospect
replace all company with proper(company);
for not isupper(company)

https://www.lianja.com/doc/index.php/ISUPPER()


Convert characters to lower case

? lcase("Lianja")
lianja

https://www.lianja.com/doc/index.php/LCASE()


Convert characters to lower case

? lower("Lianja")
lianja

https://www.lianja.com/doc/index.php/LOWER()


Convert words to lower case with the first character in upper case

? proper("ACCOUNTS")
Accounts

https://www.lianja.com/doc/index.php/PROPER()


Convert words to lower case with the first character in upper case

? ucfirst("this is THE title")
This Is The Title

https://www.lianja.com/doc/index.php/UCFIRST()


Convert characters to lower case

? strtolower("Lianja")
lianja
 
// Another Example
use accounts
index on strtolower(company) to company
replace company with "Company Name"
seek "company name"
? found()
.T.

https://www.lianja.com/doc/index.php/STRTOLOWER()


Convert character expression to upper case

? strtoupper("Lianja")
LIANJA

https://www.lianja.com/doc/index.php/STRTOUPPER()


 

Advertisements

String – search [examples]

Starting position of one character expression in another character expression
? charindex("is","This is a test")
          3
 
open database southwind
select charindex(",",notes) from employees
          1
        231
          1
          1
        248
         73
        240
          1
        109

https://www.lianja.com/doc/index.php/CHARINDEX()


Case-sensitive search of a string to determine if it ends with a specified character string

open database southwind
use orders
list for endswith(customerid,"AT")

https://www.lianja.com/doc/index.php/ENDSWITH()


Numeric position of the first occurrence of a specified character string in another character string

open database southwind
use orders
list for indexof(customerid,"AT") = 3

https://www.lianja.com/doc/index.php/INDEXOF()


Case-sensitive search of a string to determine if it starts with a specified character string

open database southwind
use orders
list for startswith(customerid,"BON")

https://www.lianja.com/doc/index.php/STARTSWITH()


Case-sensitive search of a string to determine if it contains a specified character string

open database southwind
use orders
list for substringof(customerid,"AT")

https://www.lianja.com/doc/index.php/SUBSTRINGOF()


Search for and replace text within a character string or memo field

? strtran("Hello World", "ello", "i",1,1,1)
Hi World

https://www.lianja.com/doc/index.php/STRTRAN()


Substring extraction

m_usrname = "GTW  , Gary T West  "
? substr(m->m_usrname,at(",",m->m_usrname)+1)
Gary T West

https://www.lianja.com/doc/index.php/SUBSTR()


Substring extraction

m_usrname = "GTW  , Gary T West  "
? mid(m->m_usrname,at(",",m->m_usrname)+1)
Gary T West

https://www.lianja.com/doc/index.php/MID()


 

String – null [examples]

Specify an alternative value for a null expression

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
SELECT lastname, ifnull(firstname,"Unknown") from nullon

https://www.lianja.com/doc/index.php/IFNULL()


Test if an expression evaluates to NULL and optionally specify an alternative value for a null expression

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
SELECT lastname, isnull(firstname,"Unknown") from nullon
SELECT lastname, isnull(firstname) from nullon

https://www.lianja.com/doc/index.php/ISNULL()


Specify an alternative value for a null expression

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
SELECT lastname, nvl(firstname,"Unknown") from nullon

https://www.lianja.com/doc/index.php/NVL()


Specify the text displayed for NULL values

set sql to vfp
set null on
set heading off
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
list off
.NULL.    Smith
 
set nulldisplay to "<null>"
list off
<null>    Smith
 
set nulldisplay to
list off
.NULL.    Smith

https://www.lianja.com/doc/index.php/SET_NULLDISPLAY


Return the first non-null value from a list, or .NULL. if all values in the list evaluate to NULL

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
SELECT lastname, coalesce(firstname,"Unknown") from nullon

https://www.lianja.com/doc/index.php/COALESCE()


Predicate to determine whether the specified expression is NULL

SELECT name, address, balance, cost*1.15;
  FROM accounts;
  WHERE paid_date < date() AND ord_value IS NULL;
  ORDER BY name, paid_date

https://www.lianja.com/doc/index.php/NULL_Predicate


 

String – empty/spaces [examples]

Remove trailing blanks

fname = "Christopher         "
sname = "Thompson    "
? len(fname)
        20
? len(trim(fname))
        11
? trim(fname)+" "+trim(sname)
Christopher Thompson

https://www.lianja.com/doc/index.php/TRIM()


Remove leading and trailing spaces

name="   Peter   "
? len(name)
        15
? name
   Peter
? alltrim(name)
Peter
? len(alltrim(name))

https://www.lianja.com/doc/index.php/ALLTRIM()


Trim spaces from left

? ltrim("   Lianja")
Lianja

https://www.lianja.com/doc/index.php/LTRIM()


Remove trailing blanks

fname = "Christopher         "
sname = "Thompson    "
? len(fname)
        20
? len(rtrim(fname))
        11
? rtrim(fname) + " " + rtrim(sname)
Christopher Thompson

https://www.lianja.com/doc/index.php/RTRIM()


Generate string of spaces

store space(10) to line1,line2,line3
? len(line1)
        10
? empty(line2)
.T.
? type("line3")
C

https://www.lianja.com/doc/index.php/SPACE()


Specify an alternative value for an empty expression

CREATE TABLE contact (firstname c(20), lastname c(20))
INSERT INTO contact (lastname) VALUES ("Smith")
SELECT lastname, evl(firstname,"Unknown") from contact

https://www.lianja.com/doc/index.php/EVL()


Check for empty value

if empty(name + address)
    dialog box "Name and address not specified."
endif

https://www.lianja.com/doc/index.php/EMPTY()


Check for empty value

if isblank(name + address)
    dialog box "Name and address not specified."
endif

https://www.lianja.com/doc/index.php/ISBLANK()


 

String – conversion [examples]

Convert logical to string

index on ltos(married)+upper(name) to married

https://www.lianja.com/doc/index.php/LTOS()


Numeric to string conversion

? str(1999.019,8,2)
 1999.02

https://www.lianja.com/doc/index.php/STR()


Convert an ASCII character to a number

? asc("123")
        49
? asc("Newcastle")
        78
nVar = asc("Newcastle")
        78
? type("nVar")
N

https://www.lianja.com/doc/index.php/ASC()


Convert a number to an ASCII character

? chr(66)
B
? chr(7)
bell = chr(7)
? type("bell")
C

https://www.lianja.com/doc/index.php/CHR()


Convert an ASCII character or characters to a hexadecimal string

? atoh("R")
52
 
? atoh("Lianja")
5265636974616C

https://www.lianja.com/doc/index.php/ATOH()


Convert a hexadecimal string to an ASCII character or characters

? htoa("52")
R
 
? htoa("5265636974616C")
Lianja

https://www.lianja.com/doc/index.php/HTOA()


Convert an ASCII character to a number

? rank("123")
        49
? rank ("Newcastle")
        78
nVar = rank ("Newcastle")
        78
? type("nVar")
N

https://www.lianja.com/doc/index.php/RANK()


Character string to numeric conversion

string = "1234"
? val(string)
      1234
set decimals to 2
set fixed on
? val("678.7615")
    678.76
? val("$123,456.78")
 123456.78

https://www.lianja.com/doc/index.php/VAL()


Return a numeric as a ten character string with leading zeros

? rowidtochar(1234)
0000000123

https://www.lianja.com/doc/index.php/ROWIDTOCHAR()


Numeric to string conversion

? strzero(1234,8)
00001234

https://www.lianja.com/doc/index.php/STRZERO()


 

String – comparison [examples]

Determines how comparisons between two character expressions are performed
set exact off
use patrons index events
// Lists all people beginning with "SMITH"
// SMITH, SMITHWAITE SMITHSON etc.
list all for name = "SMITH"
set exact on
// Lists only those people called "SMITH"
list all for name = "SMITH"

https://www.lianja.com/doc/index.php/SET_EXACT


Determines how string comparisons are performed

set strcompare off
? "HELLO " = "hello"
.F.
set strcompare on
? "HELLO " = "hello"
.T.

https://www.lianja.com/doc/index.php/SET_STRCOMPARE


Test for a character existing at the same position within two strings

cUserPrivs = "YYYNYNNY"
if chroverlap(cUserPrivs, "NNNNYNNN")
    SysMaintenanceMenu()
else
    dialog box [You are not authorized to perform this operation.]
endif

https://www.lianja.com/doc/index.php/CHROVERLAP()


Compare two strings

cUSERPRIVS = [YYYNNYNN]
if strcompare(cUSERPRIVS,'NNNNNNNYN')
    SysMaint()
else
    return
endif

https://www.lianja.com/doc/index.php/STRCOMPARE()


Case-sensitive compare of two strings

cUSERPRIVS = [YYYNNYNN]
if strcmp(cUSERPRIVS,'NNNNNNNYN')= 0
    SysMaint()
else
    return
endif

https://www.lianja.com/doc/index.php/STRCMP()


Case-insensitive compare of two strings

cUSERPRIVS = [yyynnynn]
if strcasecmp(cUSERPRIVS,'NNNNNNNYN')= 0
    SysMaint()
else
    return
endif

https://www.lianja.com/doc/index.php/STRCASECMP()


 

String [examples]

Determines whether non-string expressions are automatically converted when added to strings

set strconvert on
? "This string can add numerics and dates etc. " + 100.89 + " " + date()
 
set strconvert off
? "This string can add numerics and dates etc. " + str(100.89,6,2) + " " + etos(date())

 


// When %s is specified, the corresponding argument is converted to 
// character format (similar to specifying etos()).
// Widths correspond to the default values, e.g. numerics are 10
printf('It is %s, %s to be more precise\n',year(date()),datetime())
printf('The value of pi is %s\n',pi())
printf('They cost %s per %s\n',$99,100)
printf('Logicals can be %s or %s\n',.T.,.F.)
// Formatting sequences can contain a width, which will left pad with spaces 
printf('Right-justify and pad left: %10s this\n','Like')
// Left justify by placing a '-' directly following the '%' character 
printf('Left-justify and pad right: %-10s this\n','Like')
// %d is for numerics
printf('It is %d\n',year(date()))
// %t and %T are for formating datetime data types.
printf('It is %d, %t to be more precise\n',year(date()),datetime())
printf('It is %d, %T to be even more precise\n',year(date()),datetime())
// %f is for floating point numerics
printf('The value of pi is %f\n',pi())
// Decimal places can also be specified for floating point numerics (%f) 
printf('The value of pi to two decimal places is %4.2f\n',pi())
// %y is for formatting currency data types
printf('They cost %y per %d\n',$99,100)
printf('They cost %y per %d\n',$99,1000)
printf('They cost %y per %d\n',$99,10000)
//%l and %L are for formatting logical datatypes.
printf('Logicals can be %l or %l\n',.T.,.F.)
printf('Logicals can also be %L or %L\n',.T.,.F.)
// When %s is specified, the corresponding argument is converted to 
// character format (similar to specifying etos()).
// Widths correspond to the default values, e.g. numerics are 10
cVAR=sprintf('It is %s, %s to be more precise',year(date()),datetime())
echo cVAR
cVAR=sprintf('The value of pi is %s',pi())
echo cVAR
cVAR=sprintf('They cost %s per %s',$99,100)
echo cVAR
cVAR=sprintf('Logicals can be %s or %s',.T.,.F.)
echo cVAR
// Formatting characters can contain a width, which will left pad with spaces 
cVAR=sprintf('Right-justify and pad left: %10s this','Like')
echo cVAR
// Left justify by placing a '-' directly following the '%' character 
cVAR=sprintf('Left-justify and pad right: %-10s this','Like')
echo cVAR
// %d is for numerics
cVAR=sprintf('It is %d',year(date()))
echo cVAR
// %t and %T are for formating datetime data types.
cVAR=sprintf('It is %d, %t to be more precise',year(date()),datetime())
echo cVAR
cVAR=sprintf('It is %d, %T to be even more precise',year(date()),datetime())
echo cVAR
// %f is for floating point numerics
cVAR=sprintf('The value of pi is %f',pi())
echo cVAR
// Decimal places can also be specified for floating point numerics (%f)
cVAR=sprintf('The value of pi to two decimal places is %4.2f',pi())
echo cVAR
// %y is for formatting currency data types
cVAR=sprintf('They cost %y per %d',$99,100)
echo cVAR
cVAR=sprintf('They cost %y per %d',$99,1000)
echo cVAR
cVAR=sprintf('They cost %y per %d',$99,10000)
echo cVAR
//%l and %L are for formatting logical datatypes.
cVAR=sprintf('Logicals can be %l or %l',.T.,.F.)
echo cVAR
cVAR=sprintf('Logicals can also be %L or %L',.T.,.F.)
echo cVAR
fp=fcreate('fprintf.txt')
// When %s is specified, the corresponding argument is converted to 
// character format (similar to specifying etos()).
// Widths correspond to the default values, e.g. numerics are 10
fprintf(fp,'It is %s, %s to be more precise\n',year(date()),datetime())
fprintf(fp,'The value of pi is %s\n',pi())
fprintf(fp,'They cost %s per %s\n',$99,100)
fprintf(fp,'Logicals can be %s or %s\n',.T.,.F.)
// Formatting characters can contain a width, which will left pad with spaces 
fprintf(fp,'Right-justify and pad left: %10s this\n','Like')
// Left justify by placing a '-' directly following the '%' character 
fprintf(fp,'Left-justify and pad right: %-10s this\n','Like')
// %d is for numerics
fprintf(fp,'It is %d\n',year(date()))
// %t and %T are for formating datetime data types.
fprintf(fp,'It is %d, %t to be more precise\n',year(date()),datetime())
fprintf(fp,'It is %d, %T to be even more precise\n',year(date()),datetime())
// %f is for floating point numerics
fprintf(fp,'The value of pi is %f\n',pi())
// Decimal places can also be specified for floating point numerics (%f)
fprintf(fp,'The value of pi to two decimal places is %4.2f\n',pi())
// %y is for formatting currency data types
fprintf(fp,'They cost %y per %d\n',$99,100)
fprintf(fp,'They cost %y per %d\n',$99,1000)
fprintf(fp,'They cost %y per %d\n',$99,10000)
//%l and %L are for formatting logical datatypes.
fprintf(fp,'Logicals can be %l or %l\n',.T.,.F.)
fprintf(fp,'Logicals can also be %L or %L\n',.T.,.F.)
fclose(fp)

It is 2009, 11/11/2009 11:41:51 AM to be more precise
The value of pi is 3.1415926
They cost $99.0000 per 100
Logicals can be True or False
Right-justify and pad left: Like this
Left-justify and pad right: Like this
It is 2009
It is 2009, 11/11/2009 11:41:51 AM to be more precise
It is 2009, Wednesday November 11 2009 11:41:51 to be even more precise
The value of pi is 3.141593
The value of pi to two decimal places is 3.14
They cost $99.0000 per 100
They cost $99.0000 per 1000
They cost $99.0000 per 10000
Logicals can be True or False
Logicals can also be Yes or No

https://www.lianja.com/doc/index.php/Working_With_String_Data_in_Lianja

https://www.lianja.com/doc/index.php/Working_With_String_Data_in_Recital


Return from a list the expression at the specified position

? choose(2,"One","Two","Three")
Two
 
open database southwind
select employeeid, hiredate,CHOOSE(MONTH(HireDate),'Winter','Winter', ;
'Spring','Spring','Spring','Summer','Summer','Summer','Autumn','Autumn',;
'Autumn','Winter') AS Quarter_Hired from employees
 
 EMPLOYEEID HIREDATE   QUARTER_HIRED
 
          1 09/08/2011 Autumn
          2 08/14/1992 Summer
          3 09/08/2011 Autumn
          4 05/03/1993 Spring
          5 10/08/1993 Autumn
          6 10/17/1993 Autumn
          7 01/02/1994 Winter
          8 03/05/1994 Spring
          9 11/15/1994 Autumn

https://www.lianja.com/doc/index.php/CHOOSE()


Enables or disables the use of ‘\’ to escape characters

set strescape on                                                              
? "<input type=\"text\" name=\"title\">"                                      
<input type="text" name="title">

https://www.lianja.com/doc/index.php/SET_STRESCAPE


Numeric value of a control character

abandon = ctrl('g')

https://www.lianja.com/doc/index.php/CTRL()


Return a string where certain characters or strings have been escaped out or removed

? str_escape("Grocer's apostrophe")
Grocer''s apostrophe

https://www.lianja.com/doc/index.php/STR_ESCAPE()


Evaluate an expression and return the result as a string

? etos({03/29/2003})
03/29/2003
? etos({03/29/2003},5)
03/29
 
? etos(10 * 10)
       100
? etos(0.45,3)
.45
 
? etos("Hello" + " " + "World")
Hello World
? etos("Hello" + " " + "World",5)
Hello
 
? etos(.F.)
F

https://www.lianja.com/doc/index.php/ETOS()


Evaluate an expression and return the result as a string

? tostring({03/29/2003})
03/29/2003
? tostring({03/29/2003},5)
03/29
 
? tostring(10 * 10)
       100
? tostring(0.45,3)
.45
 
? tostring("Hello" + " " + "World")
Hello World
? tostring("Hello" + " " + "World",5)
Hello
 
? tostring(.F.)
F

https://www.lianja.com/doc/index.php/TOSTRING()


Convert carriage returns

cFormat = hardcr(notepad)

https://www.lianja.com/doc/index.php/HARDCR()


Replicate characters

? replicate("-", 20)
--------------------

https://www.lianja.com/doc/index.php/REPLICATE()


Indent a character string

open database southwind
use example
strtofile(strindent(mtos(notes),1),"indented.txt")

https://www.lianja.com/doc/index.php/STRINDENT()


Wrap a string to a given line width

open database southwind
use customers
echo wordwrap(companyname,20,"<br>\n",.T.)

https://www.lianja.com/doc/index.php/WORDWRAP()


Check whether an expression is a character string

cVAR = "Hello"
? is_string(cVAR)
.T.
? is_string("World")
.T.
? is_string(tostring(date()))
.T.

https://www.lianja.com/doc/index.php/IS_STRING()


Return a string with ‘C’ style picture formatting

// When %s is specified, the corresponding argument is converted to 
// character format (similar to specifying etos()).
// Widths correspond to the default values, e.g. numerics are 10
cVAR=sprintf('It is %s, %s to be more precise',year(date()),datetime())
echo cVAR
cVAR=sprintf('The value of pi is %s',pi())
echo cVAR
cVAR=sprintf('They cost %s per %s',$99,100)
echo cVAR
cVAR=sprintf('Logicals can be %s or %s',.T.,.F.)
echo cVAR
// Formatting characters can contain a width, which will left pad with spaces 
cVAR=sprintf('Right-justify and pad left: %10s this','Like')
echo cVAR
// Left justify by placing a '-' directly following the '%' character 
cVAR=sprintf('Left-justify and pad right: %-10s this','Like')
echo cVAR
// %d is for numerics
cVAR=sprintf('It is %d',year(date()))
echo cVAR
// %t and %T are for formating datetime data types.
cVAR=sprintf('It is %d, %t to be more precise',year(date()),datetime())
echo cVAR
cVAR=sprintf('It is %d, %T to be even more precise',year(date()),datetime())
echo cVAR
// %f is for floating point numerics
cVAR=sprintf('The value of pi is %f',pi())
echo cVAR
// Decimal places can also be specified for floating point numerics (%f)
cVAR=sprintf('The value of pi to two decimal places is %4.2f',pi())
echo cVAR
// %y is for formatting currency data types
cVAR=sprintf('They cost %y per %d',$99,100)
echo cVAR
cVAR=sprintf('They cost %y per %d',$99,1000)
echo cVAR
cVAR=sprintf('They cost %y per %d',$99,10000)
echo cVAR
//%l and %L are for formatting logical datatypes.
cVAR=sprintf('Logicals can be %l or %l',.T.,.F.)
echo cVAR
cVAR=sprintf('Logicals can also be %L or %L',.T.,.F.)
echo cVAR
It is       2009, 11/11/2009 11:41:51 AM to be more precise
The value of pi is  3.1415926
They cost $99.0000 per        100
Logicals can be True or False
Right-justify and pad left:       Like this
Left-justify and pad right: Like       this
It is 2009
It is 2009, 11/11/2009 11:41:51 AM to be more precise
It is 2009, Wednesday November 11 2009 11:41:51 to be even more precise
The value of pi is 3.141593
The value of pi to two decimal places is 3.14
They cost $99.0000 per 100
They cost $99.0000 per 1000
They cost $99.0000 per 10000
Logicals can be True or False
Logicals can also be Yes or No

https://www.lianja.com/doc/index.php/SPRINTF()


Display a string with ‘C’ style picture formatting

// When %s is specified, the corresponding argument is converted to 
// character format (similar to specifying etos()).
// Widths correspond to the default values, e.g. numerics are 10
printf('It is %s, %s to be more precise\n',year(date()),datetime())
printf('The value of pi is %s\n',pi())
printf('They cost %s per %s\n',$99,100)
printf('Logicals can be %s or %s\n',.T.,.F.)
// Formatting sequences can contain a width, which will left pad with spaces 
printf('Right-justify and pad left: %10s this\n','Like')
// Left justify by placing a '-' directly following the '%' character 
printf('Left-justify and pad right: %-10s this\n','Like')
// %d is for numerics
printf('It is %d\n',year(date()))
// %t and %T are for formating datetime data types.
printf('It is %d, %t to be more precise\n',year(date()),datetime())
printf('It is %d, %T to be even more precise\n',year(date()),datetime())
// %f is for floating point numerics
printf('The value of pi is %f\n',pi())
// Decimal places can also be specified for floating point numerics (%f) 
printf('The value of pi to two decimal places is %4.2f\n',pi())
// %y is for formatting currency data types
printf('They cost %y per %d\n',$99,100)
printf('They cost %y per %d\n',$99,1000)
printf('They cost %y per %d\n',$99,10000)
//%l and %L are for formatting logical datatypes.
printf('Logicals can be %l or %l\n',.T.,.F.)
printf('Logicals can also be %L or %L\n',.T.,.F.)
It is       2009, 11/11/2009 11:41:51 AM to be more precise
The value of pi is  3.1415926
They cost $99.0000 per        100
Logicals can be True or False
Right-justify and pad left:       Like this
Left-justify and pad right: Like       this
It is 2009
It is 2009, 11/11/2009 11:41:51 AM to be more precise
It is 2009, Wednesday November 11 2009 11:41:51 to be even more precise
The value of pi is 3.141593
The value of pi to two decimal places is 3.14
They cost $99.0000 per 100
They cost $99.0000 per 1000
They cost $99.0000 per 10000
Logicals can be True or False
Logicals can also be Yes or No

https://www.lianja.com/doc/index.php/PRINTF()


Test for an alphabetic character

store "965.23" to value
// If alpha assign 0
if isalpha(m->value)
    value = 0
else
    // convert to numeric
    value = val(m->value)
endif

https://www.lianja.com/doc/index.php/ISALPHA()


Check to see if the first character in a given character expression is a digit

? isdigit("9 Belmont Ave")
.T.
? isdigit("Nine Belmont Ave")
.F.

https://www.lianja.com/doc/index.php/ISDIGIT()


Formatting by using a picture string

total = 3001.23
? transform(total, "$$$$$9.99")
 $3001.23
 
? transform((0x1a * 2),"@0")
0x00000034

https://www.lianja.com/doc/index.php/TRANSFORM()


Return a character expression in reverse character sequence

open database southwind
use customers
index on reverse(customerid) tag revcust
use
set explain on
select * from customers where customerid like '%K'

https://www.lianja.com/doc/index.php/REVERSE()