Changing Character Strings 

A series of operations with character strings are available.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

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 output together.

Result

NAME	ZIP
Porter, Jenny	10580
Brown, Peter	48226
?	90019
Brian, Rose	75243
Griffith, Mary	20005

 

See also:

SQL Reference Manual, Concatenation (concatenation)

 

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,a,n), RFILL(x,a,n)

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 “Street” instead of the abbreviation “St.”

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 i^th 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