DEV Community: subrata71

Capitalizing Words in PostgreSQL: Handling Hyphenated Words and More

subrata71 — Tue, 21 Jan 2025 17:56:35 +0000

In this blog post, we’ll explore how to capitalize words in a text column using PostgreSQL while considering special cases for hyphenated words. This process involves transforming a sentence such that the first letter of each word is capitalized, and hyphenated words are properly handled by capitalizing both parts of the word.

Problem Overview

Imagine you have a table user_content with two columns: content_id and content_text. The content_text column contains sentences or phrases, and you want to:

Capitalize the first letter of each word in content_text.
For words containing hyphens (like "top-rated" or "well-known"), both parts of the word should be capitalized (e.g., "Top-Rated" and "Well-Known").
Ensure that the rest of the letters in each word are in lowercase.
This transformation is often required for proper formatting in text processing, especially when dealing with user-generated content, titles, or descriptions.

Example:

Input:

user_content table:

+------------+---------------------------------+
| content_id | content_text                    |
+------------+---------------------------------+
| 1          | hello world of SQL              |
| 2          | the QUICK-brown fox             |
| 3          | modern-day DATA science         |
| 4          | web-based FRONT-end development |
+------------+---------------------------------+

Output:

+------------+---------------------------------+---------------------------------+
| content_id | original_text                   | converted_text                  |
+------------+---------------------------------+---------------------------------+
| 1          | hello world of SQL              | Hello World Of Sql              |
| 2          | the QUICK-brown fox             | The Quick-Brown Fox             |
| 3          | modern-day DATA science         | Modern-Day Data Science         |
| 4          | web-based FRONT-end development | Web-Based Front-End Development |
+------------+---------------------------------+---------------------------------+

Solution Approach

We can solve this problem using a combination of string manipulation functions in PostgreSQL, including unnest, string_to_array, substring, and string_agg. Let’s break down the query:

Step-by-Step Explanation

Breaking Down content_text into Words:

First, we need to split the content_text into individual words. We use the string_to_array function to split the text by spaces.
We then apply the unnest function to convert the array of words into individual rows. This allows us to process each word separately.


SELECT
    content_id,
    content_text,
    unnest(string_to_array(content_text, ' ')) AS word
FROM user_content

Here, the unnest(string_to_array(content_text, ' ')) part splits the content_text into individual words, and each word is returned in a separate row.

Handling Hyphenated Words:

Now, we need to address words that contain hyphens (e.g., "top-rated").
We use a CASE statement to check if a word contains a hyphen (LIKE '%-%').
For hyphenated words, we split the word by the hyphen, capitalize each part, and then join the parts back with a hyphen.

WHEN word LIKE '%-%' THEN
    (
        SELECT string_agg(
            UPPER(substring(part, 1, 1)) || LOWER(substring(part, 2)), '-'
        )
        FROM unnest(string_to_array(word, '-')) AS part
    )

The string_to_array(word, '-') splits the hyphenated word into parts (e.g., "top-rated" becomes ['top', 'rated']).
unnest then converts the array into individual rows, and the UPPER(substring(part, 1, 1)) || LOWER(substring(part, 2)) part capitalizes each part by making the first letter uppercase and the rest lowercase.
Finally, string_agg(..., '-') joins the capitalized parts back together with a hyphen.
Regular Word Capitalization:

For words that do not contain hyphens, we simply capitalize the first letter and make the rest of the letters lowercase.

ELSE
    UPPER(substring(word, 1, 1)) || LOWER(substring(word, 2))

This ensures that for words like "hello" or "world", the first letter is capitalized, and the rest are in lowercase, turning "hello" into "Hello" and "world" into "World".

Aggregating Words Back Together:

After processing each word, we need to combine them back into a sentence. We use string_agg to aggregate the processed words into a single string, separated by spaces.

string_agg(
    CASE
        ...
    END, ' ') AS converted_text

This step ensures that all the processed words are joined together to form the final sentence.

Final Output:

Finally, we select the content_id, the original content_text, and the newly converted converted_text for each row. We also order the results by content_id to maintain the original order.

SELECT content_id, original_text, converted_text
FROM SplitWords
ORDER BY content_id;

Full Query
Putting everything together, the full query looks like this:

WITH SplitWords AS (
    SELECT
        content_id,
        content_text AS original_text,
        string_agg(
            CASE
                -- Check if the word contains a hyphen
                WHEN word LIKE '%-%' THEN
                    -- Use a lateral join to split by hyphen and capitalize each part
                    (
                        SELECT string_agg(UPPER(substring(part, 1, 1)) || LOWER(substring(part, 2)), '-')
                        FROM unnest(string_to_array(word, '-')) AS part
                    )
                ELSE
                    -- For regular words, just capitalize the first letter
                    UPPER(substring(word, 1, 1)) || LOWER(substring(word, 2))
            END, ' ') AS converted_text
    FROM (
        SELECT
            content_id,
            content_text,
            unnest(string_to_array(content_text, ' ')) AS word
        FROM user_content
    ) AS Words
    GROUP BY content_id, content_text
)
SELECT content_id, original_text, converted_text
FROM SplitWords
ORDER BY content_id;

Key Takeaways

This query uses PostgreSQL’s powerful string manipulation functions (substring, string_agg, unnest) to handle text transformations.
Hyphenated words are split, processed, and rejoined with proper capitalization.
The string_agg function is essential for rejoining words after processing.
This approach ensures that all words are correctly capitalized according to the specified rules.

Conclusion

By using PostgreSQL’s text manipulation capabilities, we can efficiently capitalize words in a sentence and handle special cases like hyphenated words. This query serves as a practical solution for text formatting in PostgreSQL, ensuring consistency and correct capitalization across various text inputs.

How to Run an Asynchronous Task in Spring WebFlux Without Blocking the Main Response?

subrata71 — Sat, 27 Jul 2024 17:04:39 +0000

I'm working with Spring WebFlux and I need to perform an asynchronous task as part of a method that should not block the main response to the user. Specifically, I want to call an asynchronous method after completing the main task, but without delaying the response.

Here's a simplified version of what I'm trying to achieve:

public Mono<ResponseDTO> publishPackage(RequestDTO requestDTO) {
    return publishPackageService.doSomething(requestDTO)
        .flatMap(responseDTO -> 
            doSomethingInAsync(requestDTO, responseDTO)
                .thenReturn(responseDTO)
        );
}

// Method that simulates an asynchronous task with a 5-second delay
public Mono<Void> doSomethingInAsync(RequestDTO requestDTO, ResponseDTO responseDTO) {
    return Mono.delay(Duration.ofSeconds(5))
        .then(); // Converts the delayed Mono<Long> to Mono<Void>
}

After this call completes, I want to execute doSomethingInAsync(requestDTO, responseDTO) asynchronously.
The doSomethingInAsync method should be non-blocking and not delay the main response.
Problem:

The doSomethingInAsync method is being executed, but it seems like it might be blocking the response or not running asynchronously as intended. How can I ensure that doSomethingInAsync runs asynchronously and does not block the response to the user?

Details:

publishPackageService.doSomething(requestDTO): Returns a Mono.
doSomethingInAsync(requestDTO, responseDTO): Is an asynchronous method that I want to run without blocking the response.

Questions:

How can I ensure doSomethingInAsync runs in the background without blocking the response?

How to Run a Method Asynchronously in a Reactive Chain in Spring WebFlux?

subrata71 — Fri, 26 Jul 2024 10:17:30 +0000

I'm trying to execute a method asynchronously within an existing reactive chain in my Project Reactor-based application. The method doUpdateLayoutInAsync is intended to perform a heavy background task, but it seems like my approach isn't working as expected. Here's my current implementation:

public Mono<Boolean> publishPackage(String branchedPackageId) {
    PackagePublishingMetaDTO publishingMetaDTO = new PackagePublishingMetaDTO();
    publishingMetaDTO.setPublishEvent(true);

    return packageRepository
            .findById(branchedPackageId, packagePermission.getPublishPermission())
            .switchIfEmpty(Mono.error(new AppsmithException(
                    AppsmithError.ACL_NO_RESOURCE_FOUND, FieldName.PACKAGE_ID, branchedPackageId)))
            .flatMap(originalPackage -> {
                String nextVersion = PackageUtils.getNextVersion(originalPackage.getVersion());

                Package packageToBePublished = constructPackageToBePublished(originalPackage);

                originalPackage.setVersion(nextVersion);
                originalPackage.setLastPublishedAt(packageToBePublished.getLastPublishedAt());
                publishingMetaDTO.setOriginPackageId(branchedPackageId);
                publishingMetaDTO.setWorkspaceId(originalPackage.getWorkspaceId());

                Mono<Void> unsetCurrentLatestMono = packageRepository.unsetLatestPackageByOriginId(originalPackage.getId(), null);
                Mono<Package> saveOriginalPackage = packageRepository.save(originalPackage);
                Mono<Package> savePackageToBePublished = packageRepository.save(packageToBePublished);

                return unsetCurrentLatestMono
                        .then(Mono.zip(saveOriginalPackage, savePackageToBePublished))
                        .flatMap(tuple2 -> {
                            Package publishedPackage = tuple2.getT2();
                            publishingMetaDTO.setPublishedPackage(publishedPackage);

                            return modulePackagePublishableService
                                    .publishEntities(publishingMetaDTO)
                                    .flatMap(publishedModules -> {
                                        if (publishedModules.isEmpty()) {
                                            return Mono.error(new AppsmithException(
                                                    AppsmithError.PACKAGE_CANNOT_BE_PUBLISHED,
                                                    originalPackage.getUnpublishedPackage().getName()));
                                        }
                                        return moduleInstancePackagePublishableService
                                                .publishEntities(publishingMetaDTO)
                                                .then(Mono.defer(() ->
                                                        newActionPackagePublishableService.publishEntities(publishingMetaDTO))
                                                        .then(Mono.defer(() ->
                                                                actionCollectionPackagePublishableService
                                                                        .publishEntities(publishingMetaDTO))));
                                    })
                                    .then(Mono.defer(() -> autoUpgradeService.handleAutoUpgrade(publishingMetaDTO)));
                        })
                        .as(transactionalOperator::transactional)
                        .then(Mono.defer(() -> doUpdateLayoutInAsync(publishingMetaDTO)));
            });
}

private Mono<Boolean> doUpdateLayoutInAsync(PackagePublishingMetaDTO publishingMetaDTO) {
    Mono<List<String>> updateLayoutsMono = Flux.fromIterable(publishingMetaDTO.getAutoUpgradedPageIds())
            .flatMap(pageId -> updateLayoutService
                    .updatePageLayoutsByPageId(pageId)
                    .onErrorResume(throwable -> {
                        log.warn("Update layout failed for pageId: {} with error: {}", pageId, throwable.getMessage());
                        return Mono.just(pageId);
                    }))
            .collectList();

    // Running the updateLayoutsMono task asynchronously
    updateLayoutsMono.subscribeOn(Schedulers.boundedElastic()).subscribe();

    return Mono.just(Boolean.TRUE);
}

Issue: I want doUpdateLayoutInAsync to run in the background while the rest of the reactive chain completes. However, the method seems to execute synchronously, and the reactive chain does not continue as expected.

Question: How can I ensure that doUpdateLayoutInAsync runs asynchronously and does not block the continuation of the reactive chain?