Background documentationInformation About Character Strings Locate this document in the navigation structure

 

You can obtain a series of information about a character string.

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

Defining the Number of Characters

SELECT name, LENGTH(name) len

  FROM hotel.customer

    WHERE title = 'Mrs' ORDER BY len, name

Part of the customer table is sorted according to the surname length, with names of the same length sorted in alphabetical order.

Result

NAME

LEN

Doe

3

Baker

5

Brian

5

Smith

5

Porter

6

Griffith

8

See also: SQL Reference Manual, LENGTH(a)

Standardizing Notation

When applied to a character string, the INITCAP function returns a character string in which the initial letter of any given word appears in uppercase and all other letters in lowercase. This function can be used to standardize the notation for names, for example.

SELECT name, INITCAP(name) name_new

  FROM hotel.customer

    WHERE firstname IS NULL

Result

NAME

NAME_NEW

Datasoft

Datasoft

TOOLware

Toolware

See also: SQL Reference Manual, INITCAP(x)

Searching the Position of Character Strings

The INDEX function returns the position of a predefined substring in the character string. You can also choose to use a third parameter in the INDEX function to specify the starting position for the search and a fourth parameter to specify the number of occurrences of the substring after which the search is to start.

SELECT name, INDEX(name,'er') position_er

  FROM hotel.customer

    WHERE title = 'Mrs'

For part of the customer table, the position of the character string ‘er’ is to be defined in the last name.

Result

NAME

POSITION_ER

Porter

5

Brian

0

Griffith

0

Smith

0

Doe

0

Baker

4

See also: SQL Reference Manual, INDEX(a,b,p,s)

Defining the Minimum and Maximum in Character Strings

You can also apply the MAX and MIN functions to character strings.

SELECT title, MIN(name) min_name

  FROM hotel.customer

    GROUP BY title

The customer (male, female, or company) whose last name starts with the earliestletter, with regard to the code selected, is searched. If the initial letters of any given names are the same, the system compares the next characters in the strings.

Result

TITLE

MIN_NAME

Company

Datasoft

Mr

Brown

Mrs

Baker

See also: SQL Reference Manual, MAX/MIN

To ensure that data is correctly sorted in alphabetical order, in particular in the case of foreign characters such as umlauts, the MAPCHAR(x,n,i) function should be used.

The GREATEST/LEAST(x,y,...) functions enable the greatest or least value to be found in a list of specified values. You can also apply these functions to character strings. GREATEST('Mary','Mike','Martin') would return Mike as the result.

More Information

More examples for Data Query