The table has a column name. the column contains first and last names. How to make it so that the last name is displayed first, and then the first letter of the first name?

CodePudding user response：

You did not specify the database that you are using. This is for mySQL:

SELECT 
  CONCAT(TRIM( SUBSTR(name, LOCATE(' ', name)) ),
        ' ',  LEFT(name, 1) )as ShortName

FROM [your_table_name_here]

Given the inputs of:

Jack Smith
Mark Anthony

The query will return:

Smith J
Anthony M

CodePudding user response：

If you are using mySQL the following example will work.
See the DBfiddle link at the bottom to test and modify.
In other SQL engines you will find the same functions, but depending on which one they may have different names and syntaxes.

select
name,
concat(
  substring(
           name,
           locate(' ',name)   1,
           25) ,
  ' ',
  substring(name,1,1)
) formatted_name
from names

name           | formatted_name
:------------- | :-------------
Vladimir Putin | Putin V       
Boris Johnson  | Johnson B     

db<>fiddle here