I have a table with three fields, FirstName, LastName and EmailAddress. The EmailAddress field is already populated and follows a 100% consistent format e.g.: John.Dunne@MallWins.com, Lisa.Franklin@MallWins.com, Mary.Renaldo@MallWins.com, etc. I need to write an update query that will populate the FirstName and the LastName as one batch.
I tried this to do just the FirstName and it gives me the FirstName, but errors with the UPDATE. I also can't seem to get the LastName extracted at all:
update [a2hr].[dbo].[MallWinsRoster] set firstname = (select SUBSTRING([EmailAddress], 1,CHARINDEX('.', [EmailAddress])-1) from [a2hr].[dbo].[MallWinsRoster]) UPDATE ...
SET FirstName = PARSENAME(LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1), 1),
LastName = PARSENAME(LEFT(EmailAddress, CHARINDEX('@', EmailAddress) - 1), 2)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 28, 2021 at 2:52 pm
That works and teaches me the PARSENAME function better which I'm still trying to understand, but this is most helpful in getting me started to understanding it!
October 29, 2021 at 3:30 pm
I have a different set of data on which I want to use PARSENAME:
Jamie Pourhussin (WTA) -692
Brenda Johnson -11337
Delia Rosenstiel -11256
Rodderick Douglas LD051121 -13224
Tynesha Townsend -14014
Mikala Fenner -12881
I can get the First name and the number extracted with the below but not the last name:
SELECT ParseName(replace([agent name], ' ', '.'), 3) FROM [a2hr].[dbo].[stgOneConvoHrs] as Fname
SELECT ParseName(replace([agent name], ' ', '.'), 2) FROM [a2hr].[dbo].[stgOneConvoHrs] as Lname
SELECT ParseName(replace([agent name], ' -', '.'), 1) FROM [a2hr].[dbo].[stgOneConvoHrs] as EmpNum
October 29, 2021 at 3:38 pm
Do/can your last names include any hyphenated names? Do you have any cases where you also have middle names (or two part last names with spaces)?
If not, CHARINDEX & SUBSTRING can probably get that rather easily.
What is "(WTA)" in the first example record? The other records seem to reliably separate names from EmpNum with a hyphen, but that field seems to complicate things. Are there any other gotchas in the data?
October 29, 2021 at 3:49 pm
The last name is always the 2nd spot and the first is always the first sport and the employee number is always the number after the dash.
October 29, 2021 at 4:50 pm
This less than eloquent way works until I get a name like (t) Robert Hester -15421 :
Select Ltrim(SubString([agent name],1,Isnull(Nullif(CHARINDEX(' ',[agent name]),0),1000))) As FirstName,
Ltrim(SUBSTRING([agent name],CharIndex(' ',[agent name]),
Case When (CHARINDEX(' ',[agent name],CHARINDEX(' ',[agent name])+1)-CHARINDEX(' ',[agent name]))<=0 then 0
else CHARINDEX(' ',[agent name],CHARINDEX(' ',[agent name])+1)-CHARINDEX(' ',[agent name]) end )) as LastName,
ParseName(replace([agent name], ' -', '.'), 1) FROM [a2hr].[dbo].[stgOneConvoHrs] as numOctober 29, 2021 at 6:56 pm
Let's fall back on the old reliable dbo.DelimitedSplit8K function, to avoid doing all the parsing ourselves.
;WITH sample_data AS (
SELECT * FROM ( VALUES
('Jamie Pourhussin (WTA) -692'),
('Brenda Johnson -11337'),
('Delia Rosenstiel -11256'),
('Rodderick Douglas LD051121 -13224'),
('Tynesha Townsend -14014'),
('Mikala Fenner -12881'),
('(t) Robert Hester -15421'),
('-67890 Justincase Numbercomesfirst')
) AS data(name)
)
SELECT ca1.*
FROM sample_data sd
CROSS APPLY (
SELECT
MAX(CASE WHEN row_num = 1 THEN Item END) AS FirstName,
MAX(CASE WHEN row_num = 2 THEN Item END) AS LastName,
MAX(CASE WHEN Item LIKE '-%' THEN SUBSTRING(Item, 2, 10) END) AS num
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ItemNumber + CASE WHEN Item LIKE '-%' THEN 20 ELSE 0 END) AS row_num
FROM dbo.DelimitedSplit8K(name, ' ') ds
WHERE Item <> '' AND Item NOT LIKE '(%'
) AS query1
) AS ca1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 29, 2021 at 7:34 pm
What is the code for the function DelimitedSplit8K?
October 29, 2021 at 8:04 pm
Sorry.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K] (
@pString varchar(8000),
@pDelimiter char(1)
)
RETURNS TABLE WITH SCHEMABINDING
AS
/*SELECT * FROM dbo.DelimitedSplit8K('ab/c/def/ghijklm/no/prq/////st/u//', '/')*/
RETURN
/*Inline CTE-driven "tally table" produces values from 0 up to 10,000: enough to cover varchar(8000).*/
WITH E1(N) AS (SELECT N FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Ns(N)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
ctetally(N) AS (/* This provides the "zero base" and limits the number of rows right up front,
for both a performance gain and prevention of accidental "overruns". */
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString, 1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
), cteStart(N1) AS ( /* This returns N+1 (starting position of each "element" just once for each delimiter). */
SELECT t.N+1
FROM ctetally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
/* Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. */
SELECT ROW_NUMBER() OVER(ORDER BY s.N1) AS ItemNumber,
SUBSTRING(@pString, s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1), 0) - s.N1, 8000)) AS Item
FROM cteStart s;
/*end of function*/
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 29, 2021 at 10:43 pm
What is the code for the function DelimitedSplit8K?
\
If you'd like to learn more about it, there's a whole article on it.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply