Background documentationChanging Character Strings Locate this document in the navigation structure

 

A series of operations with character strings are available.

Prerequisites

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.

Activities

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

More examples for Data Query