String Operator and Converting Uppercase ( Interbase Tutorial # 13 )

String Operator

We can also join two or more character strings into a single string using string operator. The string to be joined can be the value of a column we retrieve, or we may add some character strings. The operator for joining some character strings is || .

Execute these some SQL statements below :

    select name || ‘ is ’ || age || ‘ years old’ from employee

and we’ll get the data output :

================================== 
Isaac Newton is 30 years old  
Blaise Pascal is 25 years old  
Archimedes is 32 years old  
George Boole is 27 years old  
Frank Borland is 31 years old  
Max Planck is 28 years old

    select ’Boolean Algebra is developed by ‘ || name from employee
    where name=’George Boole’

the output is :

==================================== 
Boolean Algebra is developed by George Boole

select name || ‘ earns USD ‘ || totalsalary || ‘ per month’
from employee, salary
where id=employee_id

the output is :

================================ 
Isaac Newton earns USD 1100 per month  
Blaise Pascal earns USD 2200 per month  
Archimedes earns USD 3300 per month  
George Boole earns USD 4400 per month  

select ‘The highest salary is USD ‘ || max(totalsalary)from salary

the output is :

====================================== 
The highest salary is USD 4400

select ‘The total expense for salary is USD ‘ || sum(totalsalary)   from salary

the output is :

================================================ 
The total expense for salary is USD 11000

Now you can try another combinations of some character strings.

Converting Uppercase

To convert character values to uppercase, we use UPPER function. When you create a domain, use CHECK constraint to ensure that the value you enter is always in uppercase.
If you want the value of ‘name’ column always in uppercase, the statement to create domain is :

    CREATE DOMAIN string30 AS varchar(30)
    CHECK (VALUE = UPPER (VALUE))

Go to Previous Tutorial or Next Tutorial