The everyday text toolkit:
| Function | Does | Example | Result |
|---|---|---|---|
SUBSTR(s, start, length) |
part of a string | substr('Sipho', 1, 1) |
S |
INSTR(s, find) |
position of find, 0 if absent |
instr('Sipho Ndlovu', ' ') |
6 |
LENGTH(s) |
number of characters | length('Zulu') |
4 |
UPPER, LOWER, INITCAP |
change case | initcap('van wyk') |
Van Wyk |
REPLACE(s, a, b) |
replace every a with b |
replace('Right-arm fast', 'Right-arm ', '') |
fast |
TRIM, LPAD, RPAD |
strip or pad | lpad('7', 3, '0') |
007 |
|| |
join strings | 'S' || '. ' || 'Ndlovu' |
S. Ndlovu |
REGEXP_SUBSTR, REGEXP_REPLACE |
the same with regular expressions |
The test case
Show the Durban squad with a scorecard-style short name: first initial, a full stop, a space, then everything after the first space ("S. Ndlovu").
Expected result
| PLAYER_NAME | SHORT_NAME |
|---|---|
| Sipho Ndlovu | S. Ndlovu |
| Pieter van Wyk | P. van Wyk |
| Ryan Govender | R. Govender |
| Thabo Mokoena | T. Mokoena |
| Johan Kruger | J. Kruger |
| Imraan Patel | I. Patel |
| Andile Zulu | A. Zulu |
| Kyle Fourie | K. Fourie |
| Yusuf Moosa | Y. Moosa |
| Wian Brits | W. Brits |
| Lwazi Dube | L. Dube |
| Ethan Naidoo | E. Naidoo |
Pieter van Wyk becomes "P. van Wyk": everything after the first space, so the "van" stays.
Way 1: SUBSTR and INSTR
select player_name,
substr(player_name, 1, 1) || '. ' || substr(player_name, instr(player_name, ' ') + 1) as short_name
from cr_players
where team_id = 4
order by player_id;INSTR(player_name, ' ') finds the first space; SUBSTR from the character after it, with no
length, takes the rest of the string.
Way 2: REGEXP_REPLACE
select player_name,
regexp_replace(player_name, '^(.)[^ ]* (.*)$', '\1. \2') as short_name
from cr_players
where team_id = 4
order by player_id;The pattern reads: from the start (^), capture one character (.), skip the rest of the first
word [^ ]*, a space, then capture everything else (.*) to the end ($). The replacement puts
group 1, a full stop and a space, and group 2 back together.
Pick Way 1 when the rule is simple and speed matters (it's faster); Way 2 when the rule has more
cases than you want to write with INSTR ("skip a middle name if there is one").
19c and 26ai
The same on both.
Gotchas
SUBSTRcounts from 1, not 0.substr(s, 0, 1)happens to work (Oracle treats 0 as 1), but don't rely on it.INSTRreturns 0 when nothing is found, so a name with no space givessubstr(name, 1): the whole name again. Decide what you want for those.- Strings in SQL use single quotes. Double quotes are for identifiers (
"Mixed Case Column").