SQL Training #44 - 46 정보
SQL Training #44 - 46본문
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
0
댓글 0개