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 ith 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