How to Test SQL Server Functions (Substring, Charindex)

select s.BusinessEntityID
    ,s.Name
    ,ct.Name AS ContractType
    ,p.Title
    ,P.FirstName
    ,P.MiddleName
    ,p.LastName
    ,p.Suffix
    ,pp.PhoneNumber
    ,pnt.Name AS PhoneNumberType
    ,ea.EmailAddress
    ,p.EmailPromotion
    From adventureworks2017.Sales_Store s
    INNER JOIN adventureworks2017.person_businessentitycontact bec
    on bec.BusinessEntityID = s.BusinessEntityID
    INNER JOIN adventureworks2017.person_contacttype ct
    ON ct.ContactTypeID = bec.ContactTypeID
    INNER JOIN adventureworks2017.person_person p
    ON p.BusinessEntityID = bec.PersonID
    LEFT OUTER JOIN adventureworks2017.person_emailaddress ea
    ON ea.BusinessEntityID = p.BusinessEntityID
    LEFT OUTER JOIN adventureworks2017.person_personphone pp
    ON pp.BusinessEntityID = p.BusinessEntityID
    LEFT OUTER JOIN adventureworks2017.person_phonenumbertype pnt
    ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID;

 

select locate('@','gustavo0@advntue-works.com')-1);

select ea.EmailAddress, substring(ea.EmailAddress, 1, locate('@',ea.EmailAddress)-1)
from adventureworks2017.person_emailaddress ea;

https://www.youtube.com/watch?v=AIqNo7XNBEg&list=PLD20298E653A970F8&index=44

Null or Empty

create table simpledb.vendor
(
    vendor_id int auto_increment primary key,
    vendor_name varchar(50),
    vendor_email varchar(50)
);

insert into simpledb.vendor (vendor_name, vendor_email) values ('Dell', '*** 개인정보보호를 위한 이메일주소 노출방지 ***');
insert into simpledb.vendor (vendor_name, vendor_email) values ('IBM', '*** 개인정보보호를 위한 이메일주소 노출방지 ***');
insert into simpledb.vendor (vendor_name, vendor_email) values ('Microsoft', '*** 개인정보보호를 위한 이메일주소 노출방지 ***');
insert into simpledb.vendor (vendor_name, vendor_email) values ('Oracle', '');
insert into simpledb.vendor (vendor_name, vendor_email) values ('Intel', NULL);
insert into simpledb.vendor (vendor_name, vendor_email) values ('Apple', '*** 개인정보보호를 위한 이메일주소 노출방지 ***');

select * from simpledb.vendor
where vendor_email is NULL
or vendor_email = '';

isnull은 2개 파라미터는 동작안합니다.

select * from simpledb.vendor
where isnull(vendor_email, '') = '';

https://www.youtube.com/watch?v=rh89W10Su58&list=PLD20298E653A970F8&index=45

SQL Isdate Function as a Filter with Cast

ISDATE()은 없고  STR_TO_DATE 를 사용해야. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

그런데 데이타 타입이 date로 지정되면 date가 아닌 값이 어떻게 들어가지?

비슷한 기능은

create table simpledb.emp
(
    id int auto_increment primary key,
    emp_name varchar(50),
    emp_birthday varchar(50)
);

insert into simpledb.emp (emp_name, emp_birthday) values ('Bob', '2001/12/12');
insert into simpledb.emp (emp_name, emp_birthday) values ('Mary', '123');
insert into simpledb.emp (emp_name, emp_birthday) values ('Jill', '2005/12/1/');
insert into simpledb.emp (emp_name, emp_birthday) values ('Jim', '1999/1/23');
insert into simpledb.emp (emp_name, emp_birthday) values ('Sue', '2011/7/15');
insert into simpledb.emp (emp_name, emp_birthday) values ('Sally', '20120315');

select * from simpledb.emp;
비슷한 기능은  cast로 하면 더 쉽게 되네요. (Field를 date로 잡지 않고, string으로 잡았을 때 사용..)

select *  from simpledb.emp
where cast(emp_birthday as date) IS NOT NULL;

select *  from simpledb.emp
where cast(emp_birthday as date) < '2006/01/01';

 

https://www.youtube.com/watch?v=Ffw7KQfQERY&list=PLD20298E653A970F8&index=46