String Manipulations in Sybase

datalength(String)
substring(String, Start, Length)
right(String, Length)
upper(String)
lower(String)
space(Length)
replicate(Character, Count)
stuff(String, Start, Length, ReplacementString)
reverse(String)
ltrim(String)
rtrim(String)
ascii(Character)
char(ASCII Code)
str(Number [, Length [, Decimals]])
soundex(String)
difference(String1, String2)
charindex(StringToFind, StringToSearch)
patindex("%pattern%", String)

You can use these functions anywhere BUT if you use them in a WHERE clause you'll generally force a table scan. Much better to store the result of a manipulation if you'll need to search on it regularly.

A note on Soundex coding...

Soundex is a system for reducing strings to codes that indicate how similar to one another they sound. The code returned by the soundex function consists of the first consonant followed by three numbers representing the remaining consonants in the word, vowels, etc are ignored. Thus 'Smith', 'Smithe' and 'Smythe' all code the same (S530, if you're interested).

Note that the same warning about using functions in WHERE clauses applies. If searching on Soundex codes is a likely requirement, create a column with the Soundex code in it and put an index on the column.

Wildcard searching.

Transact SQL recognises three wildcards...

%   matches anything (or nothing)
_   matches any single character
[] Specifies a range of valid alternative characters
    ^ in the first position means NOT
    - between any two characters indicates a range

Escaping characters.

Confusingly, in the light of the above, Transact SQL uses the square brackets to escape wildcards in strings, so the ANSI-89 SQL 'escape' command is implemented. Hence...

select *
from Prices
where discount like "%10[%]%"

is the same as...

select *
from Prices
where discount like "%10#%%" escape "#"

Concatenating strings

is done with the '+' operator as in...

select lastname + "," + firstname
from customer

which will give something along the lines of...

Smith,Miles
Jones,Fred

etc...