sqlh8.3

-- Opdracht 1 | Customers

/* Toon de cust_contact en een column met de eerste 4 karakters van de cust_contact beginnend vanaf positie 3. */

--	cust_contact		(No column name)
--	John Smith          hn S
--	Michelle Green      chel
--	Jim Jones           m Jo
--	Denise L. Stephens	nise
--	Kim Howard          m Ho

SELECT cust_contact, SUBSTRING(cust_contact, 3, 4)
FROM Customers; 

-- Opdracht 2 | Customers

/* Toon de cust_contact en een column met de voornamen. */

--	cust_contact		cust_first_name
--	John Smith			John 
--	Michelle Green      Michelle 
--	Jim Jones           Jim 
--	Denise L. Stephens  Denise 
--	Kim Howard          Kim 

SELECT cust_contact, LEFT(cust_contact,CHARINDEX(' ', cust_contact)-1) as cust_first_name
FROM Customers; 

-- MySQL
SELECT cust_contact, LEFT(cust_contact,INSTR(cust_contact, ' ')-1) as cust_first_name
FROM Customers

-- Opdracht 3 | Customers

/* Toon de cust_contact en een column met de achternamen. */

--	cust_contact		cust_last_name
--	John Smith			Smith     
--	Michelle Green		Green         
--	Jim Jones			Jones    
--	Denise L. Stephens  L. Stephens       
--	Kim Howard          Howard    

SELECT cust_contact, SUBSTRING(cust_contact,  CHARINDEX(' ', cust_contact) + 1, LEN(cust_contact)) as cust_last_name
FROM Customers; 

-- MySQL
SELECT cust_contact, SUBSTRING(cust_contact,  INSTR(cust_contact, ' ') + 1, CHAR_LENGTH(cust_contact)) as cust_last_name
FROM Customers

-- Opdracht 4

/* Bestudeer de onderstaande instructies. Toon uiteindelijk alleen de bestandsnaam. */

--	(No column name)
--	Bestand.docx

DECLARE @bestand varchar(50)
SET @bestand = 'C:\Map\Submap\Bestand.docx'

SELECT @bestand;
SELECT REVERSE(@bestand);
SELECT CHARINDEX('\', REVERSE(@bestand));

SELECT RIGHT(@bestand, CHARINDEX('\', REVERSE(@bestand)) - 1)

-- MySQL
SELECT SUBSTRING_INDEX("C:/Map/Submap/Bestand.docx", "/", -1)