Changing Character Strings

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