--Question 1
--Let's have a look at a pre-built functions SUBSTRING()
SELECT substring('String to test - our function on',5,14) as result
--This returns the 14 characters, starting from position 5

--Question 2
--It is often more useful to specify a start and end position for the string we want to extract
--There is no built-in function for this, but we can write our own. It is a good idea to do 
--this manually in a script to develop your code before turning it into a function.
--a. Create 3 variables @string of type nvarchar(2000) and @startpos and @endpos, both of type int. Initialising
--their values to 'String to test - our function on',5, and 18 respectively
declare @string nvarchar(2000)='String to test - our function on'
declare @startpos int=5
declare @endpos int=18
--b. Declare another variable called length that calculates how many characters will be between our 
--   starting point and our end point (in our current example this would be 11
declare @length int = @endpos-@startpos+1
--c. Test your variables by using them in a substring function
select substring(@string,@startpos,@length) as result

--Question 3
--Now we create a funciton from this script 
--a. Creating the function. We will be passing in the string, startpos and endpos variables as parameters. 
--   and returning the same datatype as our string variable using substring.
go
CREATE FUNCTION getstring(@string nvarchar(2000),@startpos int,@endpos int) --input is stored in variables string,startpos and endpos
RETURNS nvarchar(2000) --the datatype of the value this function returns
BEGIN --the code for getstring
    declare @length int = @endpos-@startpos+1 --declare our own variable to store the length of the string we want to return
	RETURN substring(@string,@startpos,@length) --return the result of this substring calculatoin
END --The code for getstring
go
--b. Check that our function is in the Programmiblity/Fuctions/Scalar-valued Functions Folder
--   in the Object Navigator
--c. Test our function using a select statement.
SELECT dbo.getstring('String to test - our function on',5,18) as result