SQL query to get the first letter of each word and seperate it by a dot and a space

I have never really used SQL much but recent changes due to working from home is forcing me to gain some knowledge in it. I have been doing fine so far but I am now running into a problem that I can’t seem to find a solution for.

I have an excel sheet that pulls customer information trough a SQL query which runs by VBA code.

What I first needed to do is to get a full name from a customer and input this into the sheet. This works fine. I am using the following query for this:

Select concat(concat(Customer_First_Names,' '), Customer_Last_Name) FROM CustomerInformationTable where Customer_Number = &&1

This gives me the full name of a customer and spaces in between the first and last name and in between the names (the full first names are already spaced in between in the table).

Now, I got another request to not retrieve the first full first names and last name of a customer, but their initials and the last name.

For example:

Barack Hussein Obama

Would become

B. H. Obama

I need to do 2 things for this:

• I need to change my query to retrieve only the initials for each first name. Like I said, all full first names (even if a customer has more then one first name) is located in the column Customer_First_Names.

• I need to add a dot and a space after each initial.

How would I go on about this? I have been thinking about using SUBSTRING but I am struggling on how to do this if there is more then one first name.

So this is not going to work:

Select concat(substr(Customer_First_Names, 1, 1), '. ') from CustomerInformationTable where Customer_Number = &&1

My apologies if this has already been ask on the board so far, I looked but I did not find a suitable solution.

Answer

Assuming you don’t want to see 2 dots after someone who has just one first name (like J.. Smith), then here’s a solution that works in postgres. Not sure what your db is, so you may need to adjust as needed.

The ‘with’ query is splitting apart the first names, limiting to two. The ‘case’ statement then checks if the person has a second first name. If not, then only the first initial is provided and followed by a dot. Otherwise, both first initials are followed by a dot. Final results, all initials and names are separated by a space (like T. R. Smith).

So, a table looking like this:

cid first last

1 JAKE SMITH

2 TERREL HOWARD WILLIAMS

3 PHOEBE M KATES

Will produce the following results with the query below.

cid cust_name

1 J. SMITH

2 T. H. WILLIAMS

3 P. M. KATES

    with first_names as 
        (select distinct customer_number , 
        split_part(customer_first_name, ' ', 1) as first1,
        split_part(customer_first_name, ' ', 2) as first2
        from CustomerInformationTable 
        )
    select distinct customer_number,
    case
        when fn.first2 = '' then substring(fn.first1, 1, 1) || '.'
        else substring(fn.first1, 1, 1) || '. ' || substring(fn.first2, 1, 1) || '.'
    end
    || ' ' || a.customer_last_name as cust_name
    from CustomerInformationTable  a
    join first_names fn on fn.customer_number  = a.customer_number