Michael Michael - 1 month ago 6
SQL Question

Convert access mid to sql substring

I have code I'd like to convert from access language to SQL. I already converted the instr to charindex, just need to convert the mid piece. The code basically takes a field that contains an email address that is always formatted john.doe@gmail.com and makes two columns one with the first name and one with the last name. Here is the code:

SELECT sap.Description, Sum(main.Hours) AS SumOfHours,
substring(supervisor1email, 1, charindex('.', supervisor1email) - 1) AS SupervisorFirstName,
substring(supervisor1email, charindex('.', supervisor1email) + 1, charindex('@', supervisor1email) - charindex('.', supervisor1email) - 1) AS SupervisorLastName,

(SELECT SUM(sub.hours)

FROM v_MES_TcActivities sub

WHERE sub.costctr='106330'
AND sub.AttCode Not Like 'MEAL'
AND sub.clockin Between dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) and dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

AND substring(sub.supervisor1email, 1, charindex('.', sub.supervisor1email) - 1) = substring(main.supervisor1email, 1, charindex('.', main.supervisor1email) - 1)

AND substring(sub.supervisor1email, charindex('.', sub.supervisor1email) + 1, charindex('@', sub.supervisor1email) - charindex('.', sub.supervisor1email) - 1) = substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)

) AS TotalHours

FROM v_MES_TcActivities AS main
LEFT JOIN t_SAP_AttCodes AS sap
ON main.AttCode = sap.Code

WHERE main.AttCode Not Like 'MEAL'
AND main.CostCtr Like '106330'
AND main.ClockIn Between dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) and dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))

GROUP BY sap.Description, substring(main.supervisor1email, 1, charindex('.', main.supervisor1email) - 1), substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)

ORDER BY substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)


EDIT: Here is the whole code

Answer

As long as you specify the 3 arguments for substring (like you have already done for MID), you should be able to simply switch the function name itself and run the query. Oh, and if this is SQL Server, LEFT will have to be converted to substring as well.

SELECT 
    sap.Description, Sum(main.Hours) AS SumOfHours,     
    substring(supervisor1email, 1, charindex('.', supervisor1email) - 1) AS SupervisorFirstName,
    substring(supervisor1email, charindex('.', supervisor1email) + 1, charindex('@', supervisor1email) - charindex('.', supervisor1email) - 1) AS SupervisorLastName

EDIT: Here is a working mock-up, including dummy data

create table v_MES_TcActivities
(
supervisor1email varchar(100),
AttCode varchar(100),
Hours decimal(18,8),
costctr varchar(100),
clockin datetime default getdate()
);

create table t_SAP_AttCodes
(
Code varchar(100),
Description varchar(100)
);


insert into t_SAP_AttCodes (Code, Description) values ('NON-MEAL', 'not a meal');

insert into v_MES_TcActivities (supervisor1email, AttCode, costctr, Hours, clockin) values ('john.doe@gmail.com', 'NON-MEAL', '106330', 2, dateadd(hh, -2, getdate()));

And here is the exact query from above (formatted):

SELECT 
    sap.Description, Sum(main.Hours) AS SumOfHours, 
    substring(supervisor1email, 1, charindex('.', supervisor1email) - 1) AS SupervisorFirstName, 
    substring(supervisor1email, charindex('.', supervisor1email) + 1, charindex('@', supervisor1email) - charindex('.', supervisor1email) - 1) AS SupervisorLastName, 
    (
        SELECT SUM(sub.hours) 
        FROM v_MES_TcActivities sub 
        WHERE sub.costctr='106330' AND sub.AttCode Not Like 'MEAL' 
        AND sub.clockin Between dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) and dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
        AND substring(sub.supervisor1email, 1, charindex('.', sub.supervisor1email) - 1) = substring(main.supervisor1email, 1, charindex('.', main.supervisor1email) - 1) 
        AND substring(sub.supervisor1email, charindex('.', sub.supervisor1email) + 1, charindex('@', sub.supervisor1email) - charindex('.', sub.supervisor1email) - 1) = substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)
     ) AS TotalHours
FROM v_MES_TcActivities AS main
LEFT JOIN t_SAP_AttCodes AS sap 
ON main.AttCode = sap.Code
WHERE main.AttCode Not Like 'MEAL' 
AND main.CostCtr Like '106330' 
AND main.ClockIn Between dateadd(day, 1-datepart(dw, getdate()), CONVERT(date,getdate())) and dateadd(day, 8-datepart(dw, getdate()), CONVERT(date,getdate()))
GROUP BY sap.Description, substring(main.supervisor1email, 1, charindex('.', main.supervisor1email) - 1), substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)
ORDER BY substring(main.supervisor1email, charindex('.', main.supervisor1email) + 1, charindex('@', main.supervisor1email) - charindex('.', main.supervisor1email) - 1)

And the results, showing the parsed first name / last name:

enter image description here

The query obviously parses the email correctly, so I think your original question about how to use substring is pretty well answered. If you are still having problems getting your query to work, I suggest you look closely at the data in the tables to figure out if some values do not follow the "first.last@domain.name" pattern, or if you need other help with the sql, you should ask it as a separate question. Good luck!