January 24, 2017 at 11:34 pm
Comments posted to this topic are about the item Get the full name
January 25, 2017 at 1:48 am
Steve Jones - SSC Editor - Tuesday, January 24, 2017 11:34 PMComments posted to this topic are about the item Get the full name
Thought there might be a catch for two points, but there wasn't.
January 25, 2017 at 2:28 am
That was an easy one, thanks! You could also solve this query with CONCAT. 🙂
/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform
January 25, 2017 at 2:41 am
This was removed by the editor as SPAM
January 25, 2017 at 3:27 am
hakan.winther - Wednesday, January 25, 2017 2:28 AMThat was an easy one, thanks! You could also solve this query with CONCAT. 🙂
That's not true! CONCAT substs NULLS with a void string, so, the first expression is always returned as it is NOT NULL.
January 25, 2017 at 4:00 am
Yes, the 2 points made me wonder briefly; but the three wrong answers are so blatantly wrong that I find it amazing that 20% of replies so far picked one of them.
Incidentally, there is a bug in the proportions calculation:
the totals are fine correct answers 80%, incorrect answers 20% - there are 104 correct answers and 26 incorrect ones, 130 answers in all.
but "query 3 79%" is just plain wrong, since 104 is exactly 80% of 130; that can't be a rounding error unless some has been careless and decided to do the calculation in some muddle-headed way.
Tom
January 25, 2017 at 4:26 am
Carlo Romagnano - Wednesday, January 25, 2017 3:27 AMhakan.winther - Wednesday, January 25, 2017 2:28 AMThat was an easy one, thanks! You could also solve this query with CONCAT. 🙂That's not true! CONCAT substs NULLS with a void string, so, the first expression is always returned as it is NOT NULL.
Sadly true, even when CONCAT_NULL_YIELDS_NULL is ON (which surely must be the norm by now - MS has been telling us for many years that we shouldn't leave it OFF) which is a good reason to stick to + rather than CONCAT.
But even if CONCAT with null did produce null, it wouldn't avoid the need to use either COALESCE or nested IsNULLs.
Tom
January 25, 2017 at 6:01 am
I was looking for the trick as well. Nice question to get the brain moving on a sleepy morning. Thanks, Steve.
January 25, 2017 at 8:39 am
The code posted does not gracefully handle empty strings and spaces in the fields. So, every column should be wrapped in NULLIF if there are not a column constraints disallowing empty strings.
SELECT COALESCE
(
NULLIF(firstname,'') + ' ' + SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,'') + ', ' + NULLIF(suffix,''),
NULLIF(firstname,'') + ' ' + NULLIF(lastname,'') + ', ' + NULLIF(suffix,''),
NULLIF(firstname,'') + ' ' + SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,''),
NULLIF(firstname,'') + ' ' + NULLIF(lastname,''),
SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,''),
NULLIF(lastname,'')
)
FROM dbo.fullname;
January 25, 2017 at 9:03 am
Steve Jones - SSC Editor - Tuesday, January 24, 2017 11:34 PMComments posted to this topic are about the item Get the full name
Is there any reason why one shouldn't use the following:
SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALECSE(', ',suffix,'')
FROM dbo.Fullname
January 25, 2017 at 9:46 am
Julie Breutzmann - Wednesday, January 25, 2017 9:03 AMSteve Jones - SSC Editor - Tuesday, January 24, 2017 11:34 PMComments posted to this topic are about the item Get the full nameIs there any reason why one shouldn't use the following:
SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALECSE(', ',suffix,'')
FROM dbo.Fullname
There's two typos: COALECSE(', ',suffix,'')
COALECSE instead of COALESCE
and first argument is a constant ', ', the suffix never returns
January 25, 2017 at 9:49 am
Uncomment the query you want to try:WITH Fullname
AS
(
SELECT * FROM (VALUES
('Erin','Keri','Moody','Sr.')
,('Megan','Laura','Morales','B.S.')
,('George','Lena',NULL,'')
,(NULL,'Ryan','Lucas','M.D.')
,('Sheryl','Marianne','Morton','IV')
) AS V([firstname],[middlename],[lastname],[suffix])
)
--SELECT COALESCE
-- (
-- firstname, middlename, lastname, suffix
-- )
-- FROM Fullname
-- query 2
--SELECT COALESCE
-- (
-- firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname + ', ' + suffix, lastname
-- )
-- FROM Fullname
-- query 3
SELECT COALESCE
(
firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname + ', ' + suffix,
firstname + ' ' + lastname + ', ' + suffix,
firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname,
firstname + ' ' + lastname,
SUBSTRING(middlename, 1, 1) + '. ' + lastname,
lastname
)
FROM Fullname
-- query 4
--SELECT COALESCE
-- (
-- firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname + ', ' + suffix,
-- firstname + ' ',
-- SUBSTRING(middlename, 1,1 ) + '. ',
-- lastname,
-- ', ' + suffix
-- )
-- FROM Fullname
--SELECT COALESCE
-- (
-- NULLIF(firstname,'') + ' ' + SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,'') + ', ' + NULLIF(suffix,''),
-- NULLIF(firstname,'') + ' ' + NULLIF(lastname,'') + ', ' + NULLIF(suffix,''),
-- NULLIF(firstname,'') + ' ' + SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,''),
-- NULLIF(firstname,'') + ' ' + NULLIF(lastname,''),
-- SUBSTRING(NULLIF(middlename, ''), 1, 1) + '. ' + NULLIF(lastname,''),
-- NULLIF(lastname,'')
-- )
-- FROM Fullname;
--SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALESCE(', '+suffix,'')
--FROM Fullname
January 25, 2017 at 10:05 am
Carlo Romagnano - Wednesday, January 25, 2017 9:46 AMJulie Breutzmann - Wednesday, January 25, 2017 9:03 AMSteve Jones - SSC Editor - Tuesday, January 24, 2017 11:34 PMComments posted to this topic are about the item Get the full nameIs there any reason why one shouldn't use the following:
SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALECSE(', ',suffix,'')
FROM dbo.FullnameThere's two typos: COALECSE(', ',suffix,'')
COALECSE instead of COALESCE
and first argument is a constant ', ', the suffix never returns
I've corrected the typos. But my question still remains. This seems much simpler than the other options.
SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALESCE(', ' + suffix,'')
FROM dbo.Fullname
January 25, 2017 at 11:04 am
Julie, you query doesn't quite work correctly. Here's a test I wrote to check things. I have both your code and the correct answer in the "Act" part of the test. There is an issue with one of the rows with your query.
--EXEC tSQLt.NewTestClass @ClassName = N'[PersonTests]';
GO
CREATE OR ALTER PROCEDURE [PersonTests].[test Check Coalese of missing name parts]
AS
BEGIN
EXEC tSQLt.FakeTable @TableName = N'FullName', @SchemaName = N'dbo';
INSERT dbo.Fullname
(
firstname,
middlename,
lastname,
suffix
)
-- SQL Prompt formatting off
VALUES
( 'Erin', 'Keri', 'Moody', 'Sr.' ),
( 'Megan', 'Laura', 'Morales', 'B.S.' ),
( 'George', 'Lena', NULL, '' ),
( NULL, 'Ryan', 'Lucas', 'M.D.' ),
( 'Sheryl', 'Marianne', 'Morton', 'IV' )
-- SQL Prompt formatting on
CREATE TABLE #Expected ([fullname] [VARCHAR](100));
INSERT #Expected
(
fullname
)
VALUES
('Erin K. Moody, Sr.'),
('Megan L. Morales, B.S.'),
(NULL),
('R. Lucas'),
('Sheryl M. Morton, IV');
CREATE TABLE #Actual ([fullname] [VARCHAR](100));
-- run query
INSERT #Actual
(
fullname
)
SELECT
COALESCE(firstname + ' ', '')
+ COALESCE(SUBSTRING(middlename, 1, 1) + '. ', '') + lastname
+ COALESCE(', ' + suffix, '')
FROM dbo.Fullname;
--SELECT COALESCE
--(
--firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname + ', ' + suffix,
--firstname + ' ' + lastname + ', ' + suffix,
--firstname + ' ' + SUBSTRING(middlename, 1, 1) + '. ' + lastname,
--firstname + ' ' + lastname,
--SUBSTRING(middlename, 1, 1) + '. ' + lastname,
--lastname
--)
--FROM dbo.Fullname
EXEC tSQLt.AssertEqualsTable
@Expected = N'#Expected',
@Actual = N'#Actual',
@Message = N'Incorrect translaction';
END;
GO
EXEC tSQLt.Run
@TestName = '[PersonTests].[test Check Coalese of missing name parts]';
January 25, 2017 at 11:28 am
Julie Breutzmann - Wednesday, January 25, 2017 10:05 AMI've corrected the typos. But my question still remains. This seems much simpler than the other options.
SELECT COALESCE(firstname + ' ', '') + COALESCE( SUBSTRING(middlename,1, 1) + '. ','') + lastname + COALESCE(', ' + suffix,'')
FROM dbo.Fullname
Yes, at least two reasons.
The killer is that the row (NULL , 'Ryan' , 'Lucas' , 'M.D.') should produce 'R. Lucas' but your code produces 'R. Lucas, M.D.'
The other reason is that a row where middlename and firstname are both NULL should not return the suffix, but your code will - perhaps that's less of a killer because the sample data provided doesn't include such a row, but perhaps not since the specification is very clear.
Tom
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply