Properties

In this section, we cover getting and setting the properties or attributes of a string column or a column that we wish to subsequently treat as a string.

We will look at four classes of properties that we commonly need to deal with when working with string data; those are:

  • Data Type where we will cover getting and setting the data type of a column that we wish to treat as a string.
  • Length where we cover how to obtain the number of characters in each string literal in a string column.
  • Content Type where we cover how to check what is the content held in a string e.g. does the string hold an integer value.
  • Encoding where we cover how to get or set the encoding of a string or string column.
  • Locale where we cover how to get the global locale or set the locale used for a particular locale sensitive operation.

Data Type

We wish to set the data type of a literal or a column to string (character).

In this example, we wish to convert the data type of an integer column, col_1, to string.

SELECT col_1,
       CAST(col_1 AS STRING) AS col_2
FROM table_1;

Here is how this works:

We use the function CAST to convert the data type of a column to a string (character) data type. See Data Type Setting.

Length

We wish to obtain the number of characters in a string literal or of each element in a vector of strings.

SELECT col_1,
       LENGTH(col_1) AS col_2
FROM table_1;

Here is how this works:

We use the function LENGTH() to compute the number of characters in each element in the column col_1.

Content Type

We wish to check the type of characters held in a string.

In this example, we wish to check whether an element in the string column col_1 holds: a sequence of digits, a decimal, a sequence of alphanumeric characters, empty spaces.

SELECT col_1,
       REGEXP_INSTR(col_1, '^\\d*$') AS      is_int,
       REGEXP_INSTR(col_1, '^\\d*\\.?\\d*$') is_dec,
       REGEXP_INSTR(col_1, '^[[:alnum:]]*$') is_aln,
       REGEXP_INSTR(col_1, '^\\s*$')         is_spc
FROM table_1;

Here is how this works:

  • We use the function REGEXP_INSTR() along with an appropriate regular expression to check on the type of characters that make up a string. See Detecting.
  • The key elements of the regular expressions we use here are:
    • \\d to detect any digit character.
    • [[:alnum:]] to detect any alphanumeric character i.e. a letter or a digit.
    • \\s detects a white space
    • * detects 0 or more occurrences
    • ? detects 0 or 1 occurrences
    • ^ detects the start of a string
    • $ detects the end of a string
SQL
I/O