CHARAT

Gets the character located at the given index within a string. Character indexing starts at 0, so the first character is at index 0, the second at index 1, etc.

Syntax:

CHARAT(text, index)

index is the position of the value we want to find in text. If index does not exist for the text, nothing will be returned, otherwise a character will be returned.

Example:

If A1 contains "Hello" and A2 contains 1:

CHARAT(A1, A2)

returns e

If A1 contains "Hello" and A2 contains 5:

CHARAT(A1, A2)

Nothing is returned.

Text:

Index:

Result:

e

Application:

Let's imagine a company, "Global Airlines," that has a database of flight information. They want to generate a unique flight number for each flight based on a specific airport code and a sequence number. However, their legacy system only accepts flight numbers with a specific format, and they need to extract the first character of the destination airport code to be part of the flight number.

Table: Flights

	Flight ID	Origin Airport Code	Destination Airport Code	Sequence Number	New Flight Number
	A	B	C	D	E
1	101	JFK	LHR	5432	L5432
2	102	CDG	NRT	6789	N6789
3	103	SYD	LAX	1234	L1234
4	104	BNE	SIN	9876	S9876

The Goal: To generate a new flight number for each flight using the following formula:

NewFlightNumber = FirstCharacterOf(DestinationAirportCode) + SequenceNumber

This is where the CHARAT (or a similar string function, often called SUBSTR or LEFT) function comes in handy. It allows you to extract a specific character or a substring from a string.

CHARAT(DestinationAirportCode, 0): This part of the query is the key. It tells the database to get the character at index 0 (the first character) of the DestinationAirportCode string. In most programming languages and SQL databases, the index starts at 0.
CONCATENATE(...): This function concatenates (joins) the extracted character with the SequenceNumber to form the new flight number.