From Team Developer SqlWindows Wiki
Jump to: navigation, search

String datatype


Pointer2.png String vs Long String datatype Pointer.png

Is there a difference between Strings and Long Strings?

Well, basically there is no difference in TD. The Long String is not "longer" compared to String, they can both
hold the same amount of bytes or characters. The max size of a (Long) String is dependent on the max memory size of the system.
There is in fact no real fixed limitation.


When data is passed to a database (INSERT, UPDATE) or fetched from a database (SELECT) there IS a difference in String and Long String.
TD needs to know if the data needs to be handled differently when the database column will hold larger amounts of bytes/characters.
So the TD database routers will handle the data in bind variables of SQL statements differently !

Basic rule is that columns defined with more than 254 characters (VARCHARs, BLOBS, LONG etc) will need Long String datatype.

The String and Long String datatypes are interchangeable. You can assign a String variable with a Long String and visa-versa.
There will be no loss of data or other issues to expect. As long as the data stays in TD and is not send or fetched from databases
you can use the String datatype for all data manipulations.

Pointer2.png How to put trailing spaces in a string Pointer.png

If you want to append spaces to a string up until a certain length, use this function

   sResult = VisStrPad( sSource, nResultStringSize )

You can find this function in vtstr.apl

Example : When you have a string with 3 characters and want to add trailing spaces up until the result string is 5 characters

   Set sSource = "ABC"      ! Source has 3 characters
   Set sResult = VisStrPad( sSource, 5 )

   ! sResult has the value "ABC  "

When the source string is greater than the requested size, the source string will be truncated.
This means the result string will always be the same size as the nResultStringSize parameter.

Pointer2.png How to put prefix characters in a string Pointer.png

If you want to place prefix characters to a string use this function.

Function: PALStrPrefix
      String: psToPrefix
      String: psPrefixCharacter
      Number: pnTotalLength
   Local variables
      Number: nFieldLength
      Set nFieldLength = SalStrLength( psToPrefix )
      If nFieldLength < pnTotalLength
         Return SalStrRepeatX( SalStrLeftX( psPrefixCharacter, 1 ), pnTotalLength - nFieldLength ) || psToPrefix
      Else If nFieldLength = pnTotalLength
         Return psToPrefix
         Return SalStrLeftX( psToPrefix, pnTotalLength )

Example : to put an X character as prefix to a string with 3 characters up until it has a resulting length of 5 characters

   Set sSource = "ABC"      ! Source has 3 characters
   Set sResult = PALStrPrefix( sSource, "X", 5 )

   ! sResult has the value "XXABC"

Here you can download a sample:

Pointer2.png An alternative way to define a string in boolean expressions Pointer.png

Look at the next piece of code

   If bOk = TRUE
      Set sValue = "It seems all is OK"
      Set sValue = "An error"

It can be rewritten using one line of code using this function from vtstr.apl

   sResult = VisStrChoose( bExpression, sTrueString, sFalseString )

So the If/Else construction above can be rewritten to

   Set sValue = VisStrChoose( bOk = TRUE, "It seems all is OK", "An error" )

See also:

MediaWikiLink.png An alternative way to define a number in boolean expressions

Pointer2.png How to define and use a newline in strings Pointer.png

A nice way to define a newline is to create a string constant in your source.

String: NEWLINE = '

When entering this constant, press CTRL-ENTER after the first apostrophe.

Here a few samples using this constant.

   Set sText = "This is the first line" || NEWLINE || "This is the second line" || NEWLINE || "This is the third line"
   If SalStrScan( sText, NEWLINE ) > -1
      ! The string contains newlines, change the newlines into comma's
      Set sResult = VisStrSubstitute( sText, NEWLINE, ",")

Pointer2.png Newline representation Pointer.png

A newline in a string is represented as two characters, the CR (Carriage Return) followed by LF (Line Feed).

The CR character has ASCII value 0x0D
The LR character has ASCII value 0x0A

To construct a newline from the ASCII values

   sNewline = SalNumberToChar (0x0D) || SalNumberToChar (0x0A)

This is the same as the next assignment

   sNewline = '

Pointer2.png High performance string fills Pointer.png

See this article:

MediaWikiLink.png How to fill strings with a specific character

Pointer2.png Fast case-insensitive string comparison Pointer.png

Look at this piece of code

   Set sString1 = "HELLO"
   Set sString2 = "hello"
   If sString1 = sString2 
      ! Strings are equal
      ! Strings are not equal

In TD, string comparison like this is case-sensitive. So in the example, the strings are not equal.

To ignore case, you could write

   ! Converting to uppercase (or lowercase) 
   If SalStrUpperX( sString1 ) = SalStrUpperX( sString2 ) 
      ! Strings are equal
      ! Strings are not equal

An easy and faster way is to use lstrcmpi from kernel32.
This function compares strings, the comparison is not case-sensitive.

Here the declaration (ANSI)

Library name: kernel32.dll
   Function: lstrcmpiA
      Export Ordinal: 0
         Number: INT
         String: LPCSTR
         String: LPCSTR

Here the declaration (UNICODE, TD5.1 and higher)

Library name: kernel32.dll
   Function: lstrcmpiW
      Export Ordinal: 0
         Number: INT
         String: LPCWSTR
         String: LPCWSTR

The return value of this function :

   < 0   = String1 is less than String2
   = 0   = String1 is equal to String2
   > 0   = String1 is greater than String2

So look at this sample

   Set sString1 = "HELLO"
   Set sString2 = "hello"
   If lstrcmpiA( sString1, sString2 ) = 0 
      ! Strings are equal
      ! Strings are not equal

One nice side effect, this is faster compared to the TD implementation.
The performance will even increase when the strings to compare are larger.

Here you can download the sample for ANSI TD versions:
Here the sample for TD5.x and higher (UNICODE):