2

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.

0

1 Answer 1

3

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.