SQL Server – Function to Convert First Names to Specific Format

functionssql serversql-server-2008-r2

I'm trying to figure out a way to show a name properly.

Let's say I have 2 variables:

  • @Firstname = 'Marc-Oliver'
  • @Lastname = 'Saint-Onge'

I would like to have name shown as

  • M.-O. St.Onge
  • Or at least M.-O. Saint-Onge

I know it's probably weird for you guys to understand this, as there are not a lot of similar situations in English, but in French this is much more familiar.

How could this be possible?

Update

In fact, there are many other possibilities for first name such as 'Pier-Luc', 'Marc-André', 'Marc-Antoine', etc. This is really familiar here in Quebec, in the French language. Forget about last name, I was thinking about it and I figured that it is much more specific so I won't touch it at all. I'll keep it as Saint-Onge and not St. Onge.

Best Answer

Quick and dirty, but I'm assuming you'll be putting this into a more formalizing procedure for spitting out names.

DECLARE @Firstname nvarchar(50)
DECLARE @Lastname nvarchar(50)

SET @Firstname =  'Marc-Oliver'
SET @Lastname = 'Saint-Onge'

SELECT @FirstName, @Lastname

IF CHARINDEX('-', @FirstName) <> 0
    SELECT SUBSTRING(@Firstname, 1, 1) + '.' + 
    SUBSTRING(@Firstname, CHARINDEX('-', @Firstname), 2) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname

Which produces

Marc-Oliver     Saint-Onge
--------------------------------------------------------
M.-O. Saint-Onge

Edit:

I had never really messed around with pattern matching like this before, so it's been interesting. I believe this should work for hyphens, periods, or spaces in the first name. I tested with 'Marc Oliver', 'Marc.Oliver' and 'Marc-Oliver' all of which returned 'M.-O.' for the first name.

IF @Firstname LIKE '%[-. ]%'
    SELECT SUBSTRING(@Firstname, 1, 1) + '.-' + 
    SUBSTRING(@Firstname, PATINDEX('%[-. ]%', @Firstname) + 1, 1) + '. ' + @Lastname
ELSE
    SELECT @Firstname + ' ' + @Lastname
Related Question