Mysterious tilde ~ when using APEX_STRING.FORMAT

Have you ever seen a mysterious tilde ~ when using APEX_STRING.FORMAT? Neither had I until today

It’s just an observation but when I ran this computation on an Oracle APEX Page Item…

RETURN 
TO_CLOB(
    apex_string.format(
        '%0',
    q'[Leeds United is having a remarkable 2024-25 season in the Championship, currently sitting at the top of the table. The team has shown exceptional form, particularly in their recent matches, demonstrating their dominance and determination to secure promotion. Some notable performances include: A stunning 7-0 victory against Cardiff City on February 1, 2025, where seven different players found the back of the net, showcasing the team's depth and attacking prowess. An impressive 4-0 away win at Watford on February 11, 2025, with Daniel James scoring a brace. A thrilling 2-1 comeback victory against Sunderland on February 17, 2025, with Pascal Struijk scoring both goals, including a 90+5' winner. Leeds United's home form has been particularly strong, with the team boasting an impressive goal difference of 42:7 at Elland Road. Their attacking prowess is evident, having scored multiple goals in several matches. In the FA Cup, Leeds progressed to the fourth round after a 1-0 victory over Harrogate Town but were unfortunately knocked out by Millwall in a 2-0 defeat at home. As the season progresses, Leeds United looks well-positioned to challenge for automatic promotion to the Premier League. Their next fixture is an away match against Sheffield United on February 24, 2025, which could be crucial in determining the final league standings. The team's success can be attributed to their cohesive playing style, strong defensive organization, and the outstanding performances of key players. Manager Daniel Farke has instilled a winning mentality in the squad, and the team's depth has allowed them to maintain their form despite injuries and suspensions. The passionate support from the fans at Elland Road has also played a crucial role in the team's success, creating an intimidating atmosphere for visiting teams. With just a few months left in the season, Leeds United fans are eagerly anticipating a return to the Premier League, hoping to see their beloved team compete at the highest level once again.]' 
    )
);

…I get this…

Right at the end of that textarea, see it? my P0 substitution of the word Harrogate has been truncated. The 2nd r in Harrogate was the 1000th character in the string and therefore as per the documentation, my P0 string has been truncated to p_max_length (defaulting to 1000) and the 1000th character has been exchanged for the tilde (~) character.

It goes on to say that the tilde (~) character is appended to indicate that the original value exceeded this length.

Oh, so I need to check that the the last character has been substituted for a tilde? how do I do that in code exactly? I’m not quite sure, because the output may legitimately contain tildes. I guess I should check before using APEX_STRING.FORMAT that I’m not working with large amounts of strings. I could as workaround set p_max_length to 32767, but then ideally I should be using CLOBs - and there is no FORMAT_CLOB function (but there could be if you vote for it!).

What happens if my 1000th character is genuinely a tilde?

RETURN 
TO_CLOB(
    apex_string.format(
        '%0',
    q'[Punctuation is incredibly useful in written communication, as it helps convey meaning and clarity. Without punctuation, sentences can be confusing and difficult to understand. For instance, consider the difference between "Let's eat Grandma" and "Let's eat, Grandma." The comma in the second sentence changes the meaning entirely, turning a potentially cannibalistic suggestion into a harmless invitation. Punctuation also aids in organizing thoughts and ideas within a text. It helps readers understand the structure of sentences and paragraphs, making it easier to follow complex arguments or narratives. Commas, for example, separate items in lists and set off nonessential clauses, while semicolons link closely related independent clauses. Moreover, punctuation can influence the tone and rhythm of writing. A well-placed exclamation mark can convey excitement or urgency, while a question mark invites the reader to ponder a query. Even the humble period is essential. My favourites are !"£$%^~,&*()]' 
    )
);

So in this example, I have the string…

My favourites are !"£$%^~,&*()

… and the tilde is the 1000th character in that string… what happens is...

…APEX exchanges the tilde for a tilde. Oh I give up!

Take away:

• Using APEX_STRING.FORMAT with substitutions <= 1000 chars? - Keep using APEX_STRING.FORMAT

• Using APEX_STRING.FORMAT with substitutions whose length is between 1000 and 32767 chars? - Keep using APEX_STRING.FORMAT with p_max_length set to 32767

• Using APEX_STRING.FORMAT with substitutions > 32767 chars? Stop using APEX_STRING.FORMAT and use the the REPLACE command.

ENJOY

Oh - and Happy MAR10 Day 🍄🐢🏃‍♂️

Whats the picture? Its the Pillars Past sculpture in Pateley Bridge. Visit Yorkshire!