Firstname, Lastname in Sql, Too Complex

FirstName, LastName in SQL, too complex?

How about

SELECT COALESCE(LastName + ', ' + FirstName, 
                LastName, FirstName) Name
FROM Person

if firstname or lastname is null the entire first expression (with the ,), becomes null, forcing the coalesce to examine, second, the lastname alone, and then if lastname is null, finally, the firstname alone.

First and Last name tables to increase performance?

It depends on your query workload. This is a simple form of data compression. Reducing the set of data that is needed to answer a given query can improve performance.

On the other hand you introduce overhead in many places. It is a trade-off. If you want to retrieve the values of those columns you now need to join. DML becomes slower as well.

Since the name columns are probably rather small it is hard to imagine a query workload that would benefit from this scheme.

DATA_COMPRESSION and backup compression can be alternatives. They make very different trade-offs.

Just to prove that there is merit to your scheme: Imagine very long names and a huge (TB-sized) table. The space savings would be significant because there are rather few distinct names.

SQL: parse the first, middle and last name from a fullname field

Here is a self-contained example, with easily manipulated test data.

With this example, if you have a name with more than three parts, then all the "extra" stuff will get put in the LAST_NAME field. An exception is made for specific strings that are identified as "titles", such as "DR", "MRS", and "MR".

If the middle name is missing, then you just get FIRST_NAME and LAST_NAME (MIDDLE_NAME will be NULL).

You could smash it into a giant nested blob of SUBSTRINGs, but readability is hard enough as it is when you do this in SQL.

Edit-- Handle the following special cases:

1 - The NAME field is NULL

2 - The NAME field contains leading / trailing spaces

3 - The NAME field has > 1 consecutive space within the name

4 - The NAME field contains ONLY the first name

5 - Include the original full name in the final output as a separate column, for readability

6 - Handle a specific list of prefixes as a separate "title" column

SELECT
  FIRST_NAME.ORIGINAL_INPUT_DATA
 ,FIRST_NAME.TITLE
 ,FIRST_NAME.FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
       THEN NULL  --no more spaces?  assume rest is the last name
       ELSE SUBSTRING(
                       FIRST_NAME.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)-1
                     )
       END AS MIDDLE_NAME
 ,SUBSTRING(
             FIRST_NAME.REST_OF_NAME
            ,1 + CHARINDEX(' ',FIRST_NAME.REST_OF_NAME)
            ,LEN(FIRST_NAME.REST_OF_NAME)
           ) AS LAST_NAME
FROM
  (  
  SELECT
    TITLE.TITLE
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
         THEN TITLE.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,1
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
                       )
    END AS FIRST_NAME
   ,CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)  
         THEN NULL  --no spaces @ all?  then 1st name is all we have
         ELSE SUBSTRING(
                         TITLE.REST_OF_NAME
                        ,CHARINDEX(' ',TITLE.REST_OF_NAME)+1
                        ,LEN(TITLE.REST_OF_NAME)
                       )
    END AS REST_OF_NAME
   ,TITLE.ORIGINAL_INPUT_DATA
  FROM
    (   
    SELECT
      --if the first three characters are in this list,
      --then pull it as a "title".  otherwise return NULL for title.
      CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,1,3)))
           ELSE NULL
           END AS TITLE
      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
           ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
           END AS REST_OF_NAME
     ,TEST_DATA.ORIGINAL_INPUT_DATA
    FROM
      (
      SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(LTRIM(RTRIM(FULL_NAME)),'  ',' '),'  ',' ') AS FULL_NAME
       ,FULL_NAME AS ORIGINAL_INPUT_DATA
      FROM
        (
        --if you use this, then replace the following
        --block with your actual table
              SELECT 'GEORGE W BUSH' AS FULL_NAME
        UNION SELECT 'SUSAN B ANTHONY' AS FULL_NAME
        UNION SELECT 'ALEXANDER HAMILTON' AS FULL_NAME
        UNION SELECT 'OSAMA BIN LADEN JR' AS FULL_NAME
        UNION SELECT 'MARTIN J VAN BUREN SENIOR III' AS FULL_NAME
        UNION SELECT 'TOMMY' AS FULL_NAME
        UNION SELECT 'BILLY' AS FULL_NAME
        UNION SELECT NULL AS FULL_NAME
        UNION SELECT ' ' AS FULL_NAME
        UNION SELECT '    JOHN  JACOB     SMITH' AS FULL_NAME
        UNION SELECT ' DR  SANJAY       GUPTA' AS FULL_NAME
        UNION SELECT 'DR JOHN S HOPKINS' AS FULL_NAME
        UNION SELECT ' MRS  SUSAN ADAMS' AS FULL_NAME
        UNION SELECT ' MS AUGUSTA  ADA   KING ' AS FULL_NAME      
        ) RAW_DATA
      ) TEST_DATA
    ) TITLE
  ) FIRST_NAME

SQL Server : get a substring (first name, last name, surname)

Here you go:

CREATE TABLE [dbo].[#temp]
(
    [ID_TASK] [NVARCHAR](300) NULL,
    [CHNAME_NAME] [NVARCHAR](300) NULL,
    [CHNAME_PHONE] [NVARCHAR](300) NULL 
) ON [PRIMARY]

INSERT INTO [dbo].[#temp] 
VALUES ('ID005', 'Anderson Abreu Oliveira', '68157120'),
       ('ID006', 'Gonzalez-IV', '64106929'),
       ('ID009', 'Parker W.H.', '60994308');

WITH CTE AS
(
    SELECT [ID_TASK],
           LEFT([CHNAME_NAME], IIF( CHARINDEX(' ', [CHNAME_NAME]) = 0 , CHARINDEX('-', [CHNAME_NAME])-1, CHARINDEX(' ', [CHNAME_NAME]))) AS T,
           [CHNAME_NAME],
           [CHNAME_PHONE]
    FROM #Temp
)
SELECT [ID_TASK],
       [CHNAME_NAME],
       T AS SURNAME,
       LTRIM(REPLACE(CASE WHEN (T IS NULL) OR (T = '') THEN [CHNAME_NAME] ELSE SUBSTRING([CHNAME_NAME], LEN(T)+1, LEN([CHNAME_NAME])) END, '-', '')) FirstName,
       [CHNAME_PHONE]
FROM CTE;

Results:

+---+---------+-------------------------+-----------+-----------------+--------------+
|   | ID_TASK |       CHNAME_NAME       |  SURNAME  |    FirstName    | CHNAME_PHONE |
+---+---------+-------------------------+-----------+-----------------+--------------+
| 1 | ID005   | Anderson Abreu Oliveira | Anderson  | Abreu Oliveira |     68157120 |
| 2 | ID006   | Gonzalez-IV             | Gonzalez  | IV              |     64106929 |
| 3 | ID009   | Parker W.H.             | Parker    | W.H.           |     60994308 |
+---+---------+-------------------------+-----------+-----------------+--------------+

Demo

Filter rows on the basis of First Name + Last Name in SQL

Put all of them together

SELECT * FROM @Test WHERE (FName like '%' + @SEARCHKEYWORD + '%') OR (LName like '%' + @SEARCHKEYWORD + '%') OR (FName + ' ' + LName like '%' + @SEARCHKEYWORD + '%')

employees who work in multiple department using first name and last name

Are you looking for this query. This will give you the employees working in multiple department.

select 
    FirstName, LastName, Count(Department) 
from 
    Employee
where 
    FirstName = '<FirstName>' -- Add  condition here
    and LastName = '<LastName>'
group by 
    FirstName, LastName
having 
    Count(Department) > 1

---

Related Topics