Changing Character Strings
A series of operations with character strings are available.
You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
Concatenation
You can use the & operator to concatenate CHAR columns.
SELECT name & ', ' & firstname name, zip
FROM hotel.customer
WHERE cno < 3500
Last and first names are to be displayed together.
Result
|
NAME |
ZIP |
|
Porter, Jenny |
10580 |
|
Brown, Peter |
48226 |
|
? |
90019 |
|
Brian, Rose |
75243 |
|
Griffith, Mary |
20005 |
See also: SQL Reference Manual, CONCAT(x,y)
Removing and Inserting Characters
You can use the TRIM, LTRIM and RTRIM functions to remove and insert characters.
SELECT RTRIM(title,'any') title, name
FROM hotel.customer
WHERE title ='Company'
Specifying an abbreviated company title
Result
|
TITLE |
NAME |
|
Comp |
Datasoft |
|
Comp |
TOOLware |
See also: SQL Reference Manual, TRIM(x,y), LTRIM(x,y), RTRIM(x,y)
Abbreviating Values
SELECT title, SUBSTR(firstname,1,1)& '. '&name name
FROM hotel.customer
WHERE firstname IS NOT NULL
Specifying an abbreviated first name that is concatenated with the last name
Result
|
TITLE |
NAME |
|
Mrs |
J. Porter |
|
Mr |
P. Brown |
|
Mrs |
R. Brian |
|
Mrs |
M. Griffith |
|
Mr |
M. Randolph |
|
Mrs |
S. Smith |
|
Mr |
M. Jackson |
|
Mrs |
R. Doe |
|
Mr |
G. Howe |
|
Mr |
F. Miller |
|
Mrs |
S. Baker |
|
Mr |
J. Peters |
|
Mr |
A. Jenkins |
See also: SQL Reference Manual, SUBSTR(x,a,b)
Moving Values
You can use the LFILL or RFILL functions to fill values of the type CHAR for a specified length with any character.
SELECT LFILL(firstname,' ',10) firstname, name
FROM hotel.customer
WHERE firstname IS NOT NULL AND title = 'Mrs'
Specifying a first name that has been moved to the right; the first name and blank characters are to result in a total character string length of 10
Result
|
FIRSTNAME |
NAME |
|
Jenny |
Porter |
|
Rose |
Brian |
|
Mary |
Griffith |
|
Sally |
Smith |
|
Rita |
Doe |
|
Susan |
Baker |
See also: SQL Reference Manual, LFILL(x,n,a), RFILL(x,n,a)
EXPAND(x,n) expands a character string by a specified number of blank characters.
Replacements in Character Strings
You can use the REPLACE function to replace one character string with another in the specified column.
SELECT hno, zip, REPLACE(address,'St.','Street') address
FROM hotel.hotel
WHERE zip > '40000'
Specifying the address with Streetinstead of the abbreviation Str.
Result
|
HNO |
ZIP |
ADDRESS |
|
10 |
20005 |
155 Beechwood Street |
|
20 |
11788 |
1499 Grove Street |
|
30 |
20037 |
477 17th Avenue |
|
40 |
10019 |
112 8th Avenue |
|
70 |
12203 |
65 Yellowstone Dr. |
|
80 |
10019 |
12 Barnard Street |
|
90 |
33575 |
200 Yellowstone Dr. |
|
100 |
32018 |
1980 34th Street |
|
110 |
33441 |
111 78th Street |
You can use the TRANSLATE function to replace individual letters with other letters in the specified column. Each occurrence of the i th letter in the first character string is replaced by the ith letter in the second one.
SELECT name, TRANSLATE(name,'ae','xy') name_new
FROM hotel.customer
WHERE cno > 4000
The letters a and e are to be replaced by x and y.
Result
|
NAME |
NAME_NEW |
|
Baker |
Bxkyr |
|
Peters |
Pytyrs |
|
TOOLware |
TOOLwxry |
|
Jenkins |
Jynkins |
See also: SQL Reference Manual, REPLACE(x,y,z), TRANSLATE(x,y,z)
More examples for Data Query